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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nuemio
Frequent Visitor

Why does this measure work?

This is more of a "Why does this work" question rather than a "how do I" question.

Here is some context of the scenario using dummy data:

 

I have a fact table that looks like this:

Nuemio_0-1718105322504.png

 

Each column ID points to its respective dimension table

 

Nuemio_1-1718105703669.png

 

Within those dimension tables, the corresponding ID determines a value. For example the Risk Score Value column in the Risk Score dimension (Relationship = Risk Score_ID -> AnalysisCodeValueID)

Nuemio_3-1718105879013.png

 

I have created two measures:

1. Risk Score SUM = CALCULATE(SUM('Risk Score'[Risk Score]))

2. Total Risk Score = CALCULATE(SUMX('Analysis Codes',RELATED('Risk Score'[Risk Score])))

 

When I bring these elements into a table visual why does the Measure: "Risk Score SUMXRelated" return the correct sum but the Risk Score SUM measure does not?

 

Nuemio_4-1718106373226.png

Any help would be appreciated

 

 

1 ACCEPTED SOLUTION
Kaviraj11
Solution Specialist
Solution Specialist

Hi,

 

I see the issue here, in AnalysisCodes Table - you have two rows but only one row in Risk Score for each for RiskScoreID. When you calculate the Risk Score Measure in Risk Score, it's taking consideration of only one row. However, you are already getting correct data from 

Total Risk Score as the relationship between the two are met. Hence, the measure you are creating for the Risk Score Measure based on the Risk Score table will never match with the other measure if you are not including relationship between the AnalysisCodes table.

View solution in original post

6 REPLIES 6
Kaviraj11
Solution Specialist
Solution Specialist

Would you able to share the pbix file with dummy data?

Kaviraj11
Solution Specialist
Solution Specialist

Hi,

 

The Risk Score SUM measure you’ve created uses the SUM function within a CALCULATE statement. This measure sums up the ‘Risk Score’[Risk Score] column, but it doesn’t iterate over each row of your ‘Analysis Codes’ table. As a result, it might not consider the row context properly when calculating totals, especially if there are filters or slicers affecting the visual.

On the other hand, the Total Risk Score measure uses SUMX with a RELATED function, which iterates over each row in the ‘Analysis Codes’ table and sums the related ‘Risk Score’[Risk Score] for each row. This row-by-row operation ensures that the context is considered for each individual calculation, leading to a correct total.

 

To fix the issue with the Risk Score SUM measure, you might need to adjust it to ensure it respects the row context. One way to do this is by using SUMX over a virtual table that includes all the necessary rows and relationships. Here’s an example of how you might modify your measure:

 

Risk Score SUM Corrected =
SUMX(
RELATEDTABLE('Risk Score'),
'Risk Score'[Risk Score]
)

 

Let me know the result. 

Hi Kaviraj,

Thanks for your response, unfortunately the right number still wasn't shown when the new dax was used:

Nuemio_0-1718112206153.png

 

I think I see the problem:

I have replicated the slicers in the data view. A RiskScore_ID of 17 = 5 and a RiskScore_ID of 317 = 15. 

If I have two rows of each then the sum should total 40. However, it's summing the distinct values instead. (One row of 5 and one row of 15)

Nuemio_1-1718112586767.png

 

 

 

Kaviraj11
Solution Specialist
Solution Specialist

Hi,

 

I see the issue here, in AnalysisCodes Table - you have two rows but only one row in Risk Score for each for RiskScoreID. When you calculate the Risk Score Measure in Risk Score, it's taking consideration of only one row. However, you are already getting correct data from 

Total Risk Score as the relationship between the two are met. Hence, the measure you are creating for the Risk Score Measure based on the Risk Score table will never match with the other measure if you are not including relationship between the AnalysisCodes table.

Ah I see, that makes sense. Thanks a lot

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.