Forum Discussion
DAX Query, ALL function performance issue.
Hello,
Is there any way to rewrite the below qeury in optimize way, its taking lot of time to return the result in tabular report.
Calculating total order for each product, across time,country and employee.
Total Order Measure = CALCULATE(
COUNT(Fact[dimOrderID]),OR(Fact[OrderType]="aaa",Fact[OrderType]="bbb")
,ALL(dimDate[dimDateID])
,ALL(dimCountry),ALL(dimEmployee)
)
Table would be like below. and Totalorder will be change by report filter.
Filter: country Filter: Date, Filter: Employee
Product TotalOrder.
Thanks,
The problem is you are removing the existing filters from Date, Employee and Country because of which CALCULATE never returns blank for combinations where there is no row in the Fact table and your code triggers the CROSSAPPLY (CROSSFJOIN) behaviour of DAX Engines which returns all the possible combinations of the columns in the visual/matrix, since available information here is limited you can use this:
Total Order Measure = IF ( NOT ISEMPTY ( Sales ), CALCULATE ( COUNTROWS ( VALUES ( Sales[ProductKey] ) ), OR ( Fact[OrderType] = "aaa", FactROI[OrderType] = "bbb" ), ALL ( dimDate[dimDateID] ), ALL ( dimCountry ), ALL ( dimEmployee ) ) )
6 Replies
The problem is you are removing the existing filters from Date, Employee and Country because of which CALCULATE never returns blank for combinations where there is no row in the Fact table and your code triggers the CROSSAPPLY (CROSSFJOIN) behaviour of DAX Engines which returns all the possible combinations of the columns in the visual/matrix, since available information here is limited you can use this:
Total Order Measure = IF ( NOT ISEMPTY ( Sales ), CALCULATE ( COUNTROWS ( VALUES ( Sales[ProductKey] ) ), OR ( Fact[OrderType] = "aaa", FactROI[OrderType] = "bbb" ), ALL ( dimDate[dimDateID] ), ALL ( dimCountry ), ALL ( dimEmployee ) ) )Thanks AntrikshSharma that was quick reply, Tested the your suggestion working well.
Can you also look at the below post if you have some suggestion.
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dax-Query-optimization/m-p/2281488
AntrikshSharma After applying the NOT ISEMPTY ( Sales ) a bug is coming.below 19301, 19302 are the dimension column values, and other 2 columns are measure, and in the 3rd column where I applied the above dax.Before appying the IF( NOT( ISEMPTY(FactROI)) below is the result which is working fine, even 19302 dimension doest have value in the first column but 3rd one still showing the result.
Now after applying the IF( NOT( ISEMPTY(FactROI)) 2nd row gone.
How to fix it without impacting the result? if I remove if condition then performance is very slow.
Thanks,
Thanks it solved my problem
Hi joepath - what is the "FactROI[OrderType]" that is included in your query? Is this necessary. I would need a model and dax studio to try by I would explore using REMOVEFILTERS instead of using the separate ALL references
Thanks Daryl-Lynch-Bzy looking into this, tried AntrikshSharma solution and its working fine now.
