powerbidaxformulapowerbi-desktop

ALL() function returns only non repeated values in PowerBI formula

We are studying Power BI and DAX by creating different scenarios to understand the results of various formulas. Right now, we are reviewing the ALL() function.

Let’s suppose we have these two simple tables:

Organization:

OrgKey OrgName
1 Org A
2 Org B
3 Org C

FactFinance

Amount OrgKey
100 1
200 2
100 1
300 3
200 2
100 1
300 3

We have created the following two measures:

  1. FactTotal1 = SUMX(ALL(FactFinance), FactFinance[Amount])
  2. FactTotal2 = SUMX(ALL(FactFinance[Amount]), FactFinance[Amount])

Here are the results we are getting:

enter image description here

We understand what is happening: in the case of the FactTotal2 measure, only the distinct values are being considered. However, it is not clear why. We thought we would get the same result based on Microsoft's definition.

enter image description here

What are we missing or misunderstanding?

Solution

  • I don't know why the Microsoft guide is worded this way, but the DAX Guide explains the behavior you are you describing for ALL():

    The following remarks are valid using ALL as a table expression:

    • Using a table argument, ALL returns all the rows of the table including any duplicated rows.
    • Using a single column argument, ALL returns all the unique values of the column.