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

 

sidhaengineer's avatar
sidhaengineer
New Member
2 years ago

Thanks it solved my problem