Forum Discussion
Helper IIIDAX simple measure issue
Hello Folks,
I have a simple DAX measure which counts the order keys based on the number of items below is the DAX.
Order_20 = Calculate(DistinctCount('Fact Order'[Order_Key] | Filter('Fact Order'[Items] <=20))
Fact table consists of ~3million records.
Power BI reports spins forever when ever this measure is included with any other dimension, I think there is a performance issue. Can you please help me out?
Thanks
Try something like this:
Order_20 = COUNTX( DISTINCT( SELECTCOLUMNS( FILTER('Fact Order'[Items]<=20), "__Order_Key",[Order_Key] ) ), [__Order_Key] )The "Filter" statement on a fact table that large will lead to performance issues. The good news is that you can use a simple filter in this CALCULATE formula instead. Try this:
Order_20 = CALCULATE ( DISTINCTCOUNT ( 'Fact Order'[Order_Key] ), 'Fact Order'[Items] <= 20 )
Thanks!
Matt Drabik
4 Replies
- az38
Community Champion
Hi sqlguru448
you are using a very strange syntax. if it is, try correct one
Order_20 = Calculate(DistinctCount('Fact Order'[Order_Key]), 'Fact Order'[Items] <=20) - Greg_Deckler
Try something like this:
Order_20 = COUNTX( DISTINCT( SELECTCOLUMNS( FILTER('Fact Order'[Items]<=20), "__Order_Key",[Order_Key] ) ), [__Order_Key] ) 
MDrabikAdvocate I
The "Filter" statement on a fact table that large will lead to performance issues. The good news is that you can use a simple filter in this CALCULATE formula instead. Try this:
Order_20 = CALCULATE ( DISTINCTCOUNT ( 'Fact Order'[Order_Key] ), 'Fact Order'[Items] <= 20 )
Thanks!
Matt Drabik
sqlguru448Thank you both for the quick response, I revised my concepts. FILTER acts as an row by row iterator hence slow. I hope the results will be same after slicing and dicing multiple attributes/dimensions w/o FILTER.
