Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hey guys & gals,
I've been struggling with a measure recently, and I dont fully understand its behaviour. Maybe one of you can enlighten me.
So I have a really simple data model, with just 2 tables. 1 table with values in weeks, and one table with the weeks and an order for the weeks.
Periods look like this: "PP" or "LY PP", periodID's are strings of numbers, so "1" and "2".
So then I create 2 measures, both use a sting comparison. Take all weeks where the program week name is "PP", and the other takes the program week ID, which is also a string, but features only numbers, so in this case PP is "1".
Here are the measures, they don't get much simpler than this 🙂
Measure using order period name =
CALCULATE(
SUM('Values'[Value]),'Order'[period]="PP")
Measure using order periodid =
CALCULATE(
SUM('Values'[Value]),'Order'[PeriodID]="1")
Now the second one gives me my expected results, namely: only give me a value when the program week is PP, for all other weeks, do not give me a value.
however, the first one does something strange, and gives me a value for LYPP as well. See here:
Does anyone know why the behaviour of these two measures is different? What's the difference between filtering on "PP" vs "1"? (changing the relationship between the tables from a repation between the ID's and the names have no impact on the end result)
I'm so confused!
It's because of the combined filter context...
In your table the rows are filtered by period name. In your measure you replace the filter context explicitly by setting 'Order'[period]="PP". That means that you replace exactly the filter that is used for the rows. Therefore the rows do not matter anymore, it just gives you the PP total for all rows.
You do not have this issue when using any other column than period name. Then the filter on the first row is: period name = "PP" and period ID = 1, that gives you a result. On the second row it's period name = "PP LY" and period id = 1 which returns nothing.
Makes sense?
Hmm I think I get what you are saying, but shouldnt the filter coming from the order table not then propagate to the values table by the relationship?
Also: is the right solution here to use KEEPFILTERS?
Like so:
Measure using values periodid =
CALCULATE(
SUM('Values'[Value]),KEEPFILTERS('Values'[period]="PP"))
Yes, the KEEPFILTERS() function could do what you want as well.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |