topic How to provide the right row context using an existing measure in DAX Commands and Tips

How to provide the right row context using an existing measure

Anonymous — Sun, 26 Dec 2021 18:25:04 GMT

Hello,

I would like to know if there is a better way to solve this situation.

I have a measure that, given a fact table, calculates scores.

ScoreRegion = CALCULATE(ROUND(DIVIDE(SUM(ScoresRegion[Orig_Num]), SUM(ScoresRegion[Orig_Denom])),2)*100)

I need to create another measure that, given a score, calculates a five point scale value depending on the target of that score.

The target is available as a column in the same ScoresRegion table.

I wanted to reuse the Score Region measure.

An iterator function

SUMX(ScoresRegion, ...

would create a row context, but I get incorrect Score Region values, since it appears that the Score would be evaluated in every row, and wont aggregate cases where there is more that one score entry.

The solution I have right now uses SUMMARIZE to create the correct 'table' and row context where to iterate, and then ADDCOLUMNS to create the desired output. I replicated this pattern on other various measures.

5p RAW Score Region = VAR AggFact = ADDCOLUMNS( SUMMARIZE( ScoresRegion ,ScoresRegion[MetricId] ,ScoresRegion[Target] ,Metric[TargetLogic] ,Metric[NoTarget] ,Metric[Inverse] ,Metric[SegmentId] ,Metric[Unit] ) , "5pScaleScoreRAW", SWITCH(TRUE() ,[Inverse] = 0 && Metric[TargetLogic] <> "n/a" , ROUND(DIVIDE([Score Region],'ScoresRegion'[Target]),2)*5 ,[Inverse] = 1 && Metric[TargetLogic] <> "n/a" , ROUND(DIVIDE( (100-[Score Region]) ,(100-[Target])),2)*5 ,BLANK()) ) RETURN SUMX(AggFact, [5pScaleScoreRAW])

I noticed that the model takes too much memory (sometimes up to 9 GB) for its size (30k score rows or so).

I was wondering if this practice is expensive in terms of memory, and if there is an other more efficient, obviouss, natural way to achieve it.

Thanks

Re: How to provide the right row context using an existing measure

AlexisOlson — Sun, 26 Dec 2021 18:20:18 GMT

I think refactoring it along these lines should be more efficient.

CALCULATE ( SUMX ( SUMMARIZE ( ScoresRegion, ScoresRegion[MetricId], ScoresRegion[Target] ), ROUND ( DIVIDE ( [Score Region], 'ScoresRegion'[Target] ), 2 ) * 5 ), KEEPFILTERS ( Metric[Inverse] = 0 ), KEEPFILTERS ( Metric[TargetLogic] <> "n/a" ) ) + CALCULATE ( SUMX ( SUMMARIZE ( ScoresRegion, ScoresRegion[MetricId], ScoresRegion[Target] ), ROUND ( DIVIDE ( 100 - [Score Region], 100 - 'ScoresRegion'[Target] ), 2 ) * 5 ), KEEPFILTERS ( Metric[Inverse] = 1 ), KEEPFILTERS ( Metric[TargetLogic] <> "n/a" ) )

I can't guarantee this exact DAX will work since I know very little about your model and how the tables are related but maybe you can massage it a bit where needed.

Re: How to provide the right row context using an existing measure

Anonymous — Sun, 26 Dec 2021 21:07:47 GMT

Hello,
thanks for your ideas.

What are the main reasons why your solution is more efficient than mine?

Thanks

Re: How to provide the right row context using an existing measure

AlexisOlson — Mon, 27 Dec 2021 16:20:15 GMT

The main reason would be that I'm iterating over fewer rows of data. This also eliminates the SWITCH.

Re: How to provide the right row context using an existing measure

Anonymous — Wed, 29 Dec 2021 01:16:22 GMT

Hi Anonymous ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.