Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to provide the right row context using an existing measure

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.

PowerBoy_0-1640520212213.png

 


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

 



4 REPLIES 4
v-xiaoyan-msft
Community Support
Community Support

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
Super User
Super User

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
Not applicable

Hello,
thanks for your ideas. 

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

Thanks

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors