Forum Discussion

gardas_swathi's avatar
gardas_swathi
Microsoft Employee
3 years ago

Performance Issue with COUNTROWS Summarize

Hi,

 

I need somehelp in rewriting my DAX Measure. Below Measure is taking atleast 2-3 mins to get the data from the tables. 

[Demand],[MonthlySupply] &[NetAvailSupplyDemand] are existing measures in the Cube with some inbuilt logic.

 

I need to get the count of distinct product ID's based on conditions used in the measure.  I have many other dimensions in the cube and below measure should be able to return data based on the selection ( like Area/ Time). How can i better write the below measure and give the same expected output?

 

For context - product table has 1.6M unique products, Demand table has 215M records and Supply table has 147M records

 

Product Coverage:=
VAR RowCount =
COUNTROWS (
FILTER (
SUMMARIZE (
'Demand Table',
'Product'[ID]
),
(IF (
(
( ISBLANK ( [Demand] ) = TRUE || [Demand] <= 0 )
&& ( ISBLANK ( [MonthlySupply] ) = TRUE || [MonthlySupply] <= 0 )
&& ( ISBLANK ([NetAvailSupplyDemand] ) = TRUE | [NetAvailSupplyDemand] <= 0 )
) || ( (ISBLANK ( [Demand] ) = TRUE || [Demand] <= 0 ) && [MonthlySupply] > 0),
-1,
IF (( ISBLANK ( [Demand] ) = FALSE || [Demand] > 0) && ( ISBLANK ( [MonthlySupply] ) = TRUE|| [MonthlySupply] = 0),-1,[NetAvailSupplyDemand])
) >= 0
)))
RETURN IF ( ISBLANK ( RowCount ) = TRUE && [NetAvailSupplyDemand] <= 0,0,RowCount )

COUNTROWS
DAX
Dax performance

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi gardas_swathi 
    Please try

    Product Coverage :=
VAR RowCount =
    COUNTROWS (
        FILTER (
            SUMMARIZE ( 'Demand Table', 'Product'[ID] ),
            VAR Demand = [Demand]
            VAR MonthlySupply = [MonthlySupply]
            VAR NetAvailSupplyDemand = [NetAvailSupplyDemand]
            RETURN
                IF (
                    (
                        (
                            ISBLANK ( Demand )
                                || Demand <= 0
                        )
                            && (
                                ISBLANK ( MonthlySupply )
                                    || MonthlySupply <= 0
                            )
                            && (
                                ISBLANK ( NetAvailSupplyDemand )
                                    || NetAvailSupplyDemand <= 0
                            )
                    )
                        || (
                            (
                                ISBLANK ( Demand )
                                    || Demand <= 0
                            )
                                && MonthlySupply > 0
                        ),
                    -1,
                    IF (
                        (
                            NOT ISBLANK ( Demand )
                                || Demand > 0
                        )
                            && (
                                ISBLANK ( MonthlySupply )
                                    || MonthlySupply = 0
                            ),
                        -1,
                        NetAvailSupplyDemand
                    )
                ) >= 0
        )
    )
RETURN
    IF ( ISBLANK ( RowCount ) && [NetAvailSupplyDemand] <= 0, 0, RowCount )

     

     

    • gardas_swathi's avatar
      gardas_swathi
      Microsoft Employee
      3 years ago

      Hi tamerj1 ,

       

      Thanks for your response. This solution has not reduced the performance significantly. Its taking 30 seconds less than it used to before. However i am seeing this error in the last line. Is it because we are using NetAvailSupplyDemand Variable outside the context of creation?

       

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    gardas_swathi Try this:

    Product Coverage :=
VAR DemandProductIDColumn =
    VALUES ( 'Demand Table'[Product ID] )
VAR NewColumns =
    ADDCOLUMNS (
        DemandProductIDColumn,
        "@Demand", [Demand],
        "@MonthlySupply", [MonthlySupply],
        "@NetAvailSupplyDemand", [NetAvailSupplyDemand]
    )
VAR FilterRows =
    FILTER (
        NewColumns,
        VAR Demand_True_LessThanZero =
            ISBLANK ( [@Demand] ) = TRUE
                || [@Demand] <= 0
        VAR MonthlySupply_True_LessThanZero =
            ISBLANK ( [@MonthlySupply] ) = TRUE
                || [@MonthlySupply] <= 0
        VAR NetAvailSupplyDemand_True_LessThanZero =
            ISBLANK ( [@NetAvailSupplyDemand] ) = TRUE
                || [@NetAvailSupplyDemand] <= 0
        VAR Demand_FalseNotZero =
            ISBLANK ( [Demand] ) = FALSE
                || [Demand] > 0
        VAR MonthlySupply_True_Zero =
            ISBLANK ( [@MonthlySupply] ) = TRUE
                || [@MonthlySupply] = 0
        VAR Result =
            IF (
                OR (
                    Demand_True_LessThanZero && MonthlySupply_True_LessThanZero
                        && NetAvailSupplyDemand_True_LessThanZero,
                    Demand_True_LessThanZero
                        && [@MonthlySupply] > 0
                ),
                -1,
                IF (
                    Demand_FalseNotZero && MonthlySupply_True_Zero,
                    -1,
                    [@NetAvailSupplyDemand]
                )
            )
        RETURN
            Result > 0
    )
VAR RowCount =
    COUNTROWS ( FilterRows )
VAR Result =
    IF ( ISBLANK ( RowCount ) = TRUE && [NetAvailSupplyDemand] <= 0, 0, RowCount )
RETURN
    Result
    • gardas_swathi's avatar
      gardas_swathi
      Microsoft Employee
      3 years ago

      Hi AntrikshSharma ,

       

      I tried to execute VAR New Columns to see the data. I observed that MonthlySupply and NetAvailSupplyDemand are not slicable by productID. Screenshot for reference. I have changed the measure names in my original post to make it simple. Below screenshot the measure names . All these tables are related to Product Table. Monthly Supply is all same for all the product ID's . Net Avail Formula is MonthlySupply-Demand, so Net Avail values seem to change a bit . But overall values in below table are incorrect. I need to get Demand, Monthly Supply and Net Avail by Product ID and then do all the checks

       

       

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        3 years ago

        gardas_swathi Can you share the screenshot of the diagram view, use VALUES ( Product'[ID] ) from product table as I forgot there are multiple facts. So VALUES ( Demand[ProductID] ) won't transfer the filter from context transition to other tables that aren't related to Demand Table.