CODEWITHSUNDEEP
powerbidax
the.scrimpton
the.scrimpton

Reputation: 25

Power BI DAX ALL function confusion

I am currently studying Power BI and DAX and I would like to understand the fundamentals as much as possible.

I am now at ALL() function. I have good understanding about it however I am a bit confused how it behaves in Power BI in one scenario, such as below. enter image description here

Table 1 and 2 are TestFact and TestDim respectively, and they relate via shiptypeid.

Using Cnt on Table 3, it gives me 4 rows. However using the CntAll in Table 4, it gives me 12 rows. It's as if it does not use the relationship on shiptypeid. My expectation is just still 4 rows but 4 for all rows.

Table 5 is the same with Table 4 but I have removed the shiptype column, and viola it gives me 4 rows. So I am thinking this likely this is due to relationship. But why is this happening? Can someone help me understand this?

Upvotes: 1

Views: 233

Answers (1)

Marcus
Marcus

Reputation: 3995

In this case you are creating a measure that calculates the same value no matter what shiptype you use, since you are lifting any filters incoming to TestFact from TestDim (and any other sources) when you use ALL().

This causes your table visualisation to expand (perhaps not intuitively) because for your first row: orderid = 8000, shipmentid = 1001, all the different ship types will have a "valid" row count in TestFact.

Whenever you use this CntAll measure with dimensions from TestDim, all the distinct combinations from TestDim will crossjoin with all distinct combinations from TestFact.

enter image description here

When you remove the shiptype column you will be left with only the distinct combinations from TestFact, which will result in 4 rows.

Upvotes: 1

Related Questions