Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
So I have questions regarding this.
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
Solved! Go to Solution.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |