Optimizing Measure performance (open incidents running total)
Dear All, A couple of days ago I posted a topic for help with a measure which calculates the number of open incidents per day. I've already made some small adjustments since then. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Open-Incidents-per-day/m-p/3968348 Objective of this measure is to calculate to number of incidents where the last status in the filter context of the visual is not closed. The measure I created works in the sense that it shows the correct results. However, already in my test-environment which is only ~15.000 rows, the performance isn't great. When I attempt to run the measure in my live environment with ~15.000.000 rows, it's completely un-useable, as in, I just get an error that it runs out of memory. As far as I can see there are 2 issues: The number of iterations on the facttable A rolling date range is intensive as the datasize keeps getting larger day-by-day (_Date[Date] <= MaxDate) I'm however at a loss on how to calculate this result in a different way. What would be the correct way to re-write this measure so that it's usable in a larger data-model? For reference, my fact table (Raw_Transactions) looks something like the screenshots below. Only connected table is a date table called '_date'. Thanks in advance for the assistance! Open Incidents (Summarize) = VAR MaxDate = MAX('_Date'[Date]) VAR Result = CALCULATE( COUNTX( ADDCOLUMNS( SUMMARIZE( Raw_Transactions, Raw_Transactions[IncidentId], Raw_Transactions[CountOfTransaction] ), "@MaxTransaction", VAR _Result = CALCULATE( MAX( Raw_Transactions[CountOfTransaction] ), ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ), _Date[Date] <= MaxDate ) RETURN IF( _Result = Raw_Transactions[CountOfTransaction], _Result ) ), [@MaxTransaction] ), _Date[Date] <= MaxDate, Raw_Transactions[TransactionType] <> "Closed" ) RETURN Result
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 )
