How to provide the right row context using an existing measure

[Anonymous]

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

[Anonymous]

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.

[AlexisOlson]

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.

[Anonymous]

Hello,
thanks for your ideas. 

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

Thanks

[AlexisOlson]

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