topic Re: DAX Query, ALL function performance issue. in DAX Commands and Tips

DAX Query, ALL function performance issue.

joepath — Fri, 18 Mar 2022 13:38:52 GMT

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,

Re: DAX Query, ALL function performance issue.

AntrikshSharma — Fri, 18 Mar 2022 11:06:19 GMT

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 ) ) )

Re: DAX Query, ALL function performance issue.

Anonymous — Fri, 18 Mar 2022 11:31:16 GMT

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

Re: DAX Query, ALL function performance issue.

joepath — Fri, 18 Mar 2022 11:51:11 GMT

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

Re: DAX Query, ALL function performance issue.

joepath — Fri, 18 Mar 2022 11:53:22 GMT

Thanks Daryl-Lynch-Bzy looking into this, tried AntrikshSharma solution and its working fine now.

Re: DAX Query, ALL function performance issue.

joepath — Mon, 18 Apr 2022 16:56:56 GMT

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,

Re: DAX Query, ALL function performance issue.

sidhaengineer — Mon, 03 Jun 2024 07:36:21 GMT

Thanks it solved my problem