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.
Hello,
I'm in that moment of questioning things I assumed in the past, so excuse me if this doubt is too obvious.
Imagine a simple scenario: a date table with a relationship to a fact table, a one-to-many relationship.
If you think in terms of relationships, if I apply a filter to the one side of the relationship it will affect the many side. But If I apply a filter on the many side it won't affect the one side, in this case the Date table.
In this sqlbi.com article it says that we don't have to think in terms of relationships but in terms of expanded tables.
Ok, but how can I choose which expanded table my measure/filter is using?
If I have the measure
Measure 1 = MAX(Date[Date])
and a slicer using the Fact[Date] column, the selection of values in this slicer is not affecting the result of Measure 1, which is always returning the MAX date of the entire table, as if no filters were applied.
This is a representation of the expanded tables given in the link before.
If Date[Date] expanded table contains its related Fact[Date] column why it is not being affected by the slicer?
Or... why Measure 1 is not calculated using the expanded table of Fact? In this case it should be affected by the slicer, won't it?
And also please consider this other scenario.
In this case a table visual it's used, with the two same elements as before: Measure 1 and Fact[Date].
As I understand, the filter context in each 'row' of the visual is modified by the date of Fact[Date], but it does not affect Date table and therefore Measure 1 always returns the max date of the unfiltered table.
What is it that I am understanding wrong?
Thanks,
PD. I know how to change this for Measure 1 to return the MAX of a filtered Date table, but this is not my question. I'm looking for an explanation about that concrete behavior in terms of expanded tables.
Solved! Go to Solution.
"If Date[Date] expanded table contains its related Fact[Date] column" - it doesn't. It's the other way around.
So Measure1 is not altered by values in the slicer.
I rarely think in terms of expanded tables (I use my own mental model with tables/relationships) so it maybe makes it clearer that Fact values in a slicer don't affect measures based on the dimension table (if relationship is 1:m from dimension).
--
if i get time i'll think about 2nd question.
"If Date[Date] expanded table contains its related Fact[Date] column" - it doesn't. It's the other way around.
So Measure1 is not altered by values in the slicer.
I rarely think in terms of expanded tables (I use my own mental model with tables/relationships) so it maybe makes it clearer that Fact values in a slicer don't affect measures based on the dimension table (if relationship is 1:m from dimension).
--
if i get time i'll think about 2nd question.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |