Forum Discussion
Relationships and Reference Measure vs Direct Formula
Hi!
I'm trying to understand why my relationships doesn't seem to be working correctly and the difference between calling a measure from another measure and directly applying the formula in a measure.
I have 2 tables with a one-to-many relationship. The first table just describes the ID, the second table has columns for ID, Year, Category, and Value.
I added 3 measures for average.
- Average = AVERAGE('VALUE'[Value])
- Average by ID Direct = AVERAGEX( VALUES('VALUE'[ID]), AVERAGE('VALUE'[Value]))
- Average by Measure = AVERAGEX( VALUES('VALUE'[ID]), [Average]))
I then created a table/matrix with 'Values'[ID], 'Description'[Description], 'Values'[Category], with measures 2 and 3, and slicers for Category and Year.
For some reason, when I select from the Year and Category slicers, the measure "Average by Measure" I get a table where the description returns 2 rows for 1 ID:
Result
So I have questions regarding this.
- Why did Measure 2 and 3 return different values when in essence they should be the same?
- Why doesn't the relationship work even though I've set the direction to filter both.
Note:
I realize that I can fix this by adding the relatedtable to measure 3, but I want to understand why. I also know that I can just use the ID from the Description table, but again, I want to understand why.
I've attached my sample in Google Drive: GDrive PBIX File
Values TableDescription Table
Relationship
Every measure has an implicit CALCULATE that you can’t see, so measures always trigger context transition from within a row context. Besides, you may try performance analyzer in Power BI Desktop to check query.
5 Replies
I think the problem , because of blank values. Please find the attached file.
Can you tell me why? Also, if I create "Measure = 1" it returns something like a cartesian join for the IDs between the tables. Why does this happen even though I have a bi-directional filter on the tables. This doesn't make sense to me, and it feels like I'm not understanding a basic concept of relationships and measures.
Try using DISTINCT instead of VALUES. VALUES and VALUE aren't the greatest functions in the world and tend to be overused. They tend to mask data modeling issues.
Thanks, but it pretty much returns the same thing. I'm also more concerned with knowing why the relationship isn't working correctly and why the using a measure in a measure returns a different value than using the same formula directly.
Every measure has an implicit CALCULATE that you can’t see, so measures always trigger context transition from within a row context. Besides, you may try performance analyzer in Power BI Desktop to check query.
