Forum Discussion

joepath's avatar
joepath
Helper II
4 years ago
Solved

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 ...
  • AntrikshSharma's avatar
    AntrikshSharma
    4 years ago

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

     

AntrikshSharma's avatar
AntrikshSharma
Community Champion
4 years ago

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

 

joepath's avatar
joepath
Helper II
4 years ago

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,