topic Re: Performance Issue with COUNTROWS Summarize in DAX Commands and Tips

Performance Issue with COUNTROWS Summarize

gardas_swathi — Fri, 03 Feb 2023 02:55:50 GMT

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 )

Re: Performance Issue with COUNTROWS Summarize

tamerj1 — Fri, 03 Feb 2023 05:26:11 GMT

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 )

Re: Performance Issue with COUNTROWS Summarize

AntrikshSharma — Fri, 03 Feb 2023 06:53:09 GMT

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

Re: Performance Issue with COUNTROWS Summarize

gardas_swathi — Fri, 03 Feb 2023 20:33:56 GMT

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?

Re: Performance Issue with COUNTROWS Summarize

gardas_swathi — Fri, 03 Feb 2023 20:42:21 GMT

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

Re: Performance Issue with COUNTROWS Summarize

AntrikshSharma — Sat, 04 Feb 2023 07:35:29 GMT

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.