Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
gardas_swathi
Microsoft Employee
Microsoft Employee

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 )

5 REPLIES 5
AntrikshSharma
Super User
Super User

@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

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

 

gardas_swathi_1-1675456618394.png

 

@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.

tamerj1
Super User
Super User

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 )

 

 

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?

gardas_swathi_0-1675456398861.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.