Need measures that will work with a larger dataset (out of memory errors)
I have 4 working measures prefixed with # in this example Power BI report. The measures work fine with the example data but when I use real data (approx. 90 million imported rows with filters applied to reduce the dataset for scenario testing) I get out of memory errors on some visuals, unless I apply more filters to reduce the dataset. Is there a way to re-write these measures so that they will work better with a large dataset? Measure 1 is an interim measure to calculate Measure 2: #RT = CALCULATE( SUMX( ADDCOLUMNS( SUMMARIZE( DummyDataID, DummyDataID[ID] ), "ExAmt", [Exceeded Amount] ), [ExAmt] ), FILTER(ALLSELECTED(DummyDataID), DummyDataID[ID] <= MAX(DummyDataDemand[ID])) ) Measure 2 #RT_filtered = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR firstID = MINX ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] ) VAR minValue = MINX ( FILTER ( DummyDataID, DummyDataID[ID] = firstID ), [Exceeded Amount] ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ), [#RT] ) ) RETURN IF ( currentID = firstID && minValue < 0, [#RT] - minValue, [#RT] - minOfSum ) Measure 3 is the sum of the values from Measure 2 #RT with sum = IF(HASONEVALUE(DummyDataID[ID]), [#RT_filtered], SUMX(VALUES(DummyDataID[ID]), [#RT_filtered])) Measure 4 is the highest value #RT max = MAXX( ADDCOLUMNS( SUMMARIZE(DummyDataID, DummyDataID[ID]), "@RT", [#RT with sum] ), [#RT with sum] )
