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 am facing a problem with relationships and FILTER direction behaviour.
I have three tables in the model - MonthYear, Dates, Table1 having below relationships among them
I have created two measures for calculating Total Amount from Table1 as below:
Measure1 = VAR SelectedMonth = IF ( ISFILTERED ( Dates[Month]), SELECTEDVALUE ( Dates[Month Num] ), IF ( ISFILTERED ( Dates[Quarter] ), MAX ( Dates[Month Num]) ) ) VAR SelectedYear = SELECTEDVALUE ( Dates[Year] ) VAR EndDate = DATE ( SelectedYear, SelectedMonth, 1 ) VAR TotalAmount = CALCULATE ( SUM ( Table1[Amount] ), FILTER ( ALL( Dates), Dates[Date] <= EndDate ) ) RETURN TotalAmount
Measure2 = VAR SelectedMonth = IF ( ISFILTERED ( Dates[Month] ), SELECTEDVALUE ( Dates[Month Num] ), IF ( ISFILTERED ( Dates[Quarter] ), MAX ( Dates[Month Num]) ) ) VAR SelectedYear = SELECTEDVALUE ( Dates[Year] ) VAR EndDate = DATE ( SelectedYear, SelectedMonth, 1 ) VAR TotalAmount = SUM ( Table1[Amount] ) RETURN TotalAmountThe only difference between both the measures is - in Measure 1, I am applying the filter on Dates table and then calculating sum(amount) as
Solved! Go to Solution.
The expanded table of Dates includes MonthYear. If you refer to the whole table dates you are referring to the expanded table and if you filter you will be filtering the MonthYear table. Then the filter will propagate from MonthYear to Table1 as usual. If you use a column of Dates, you are filtering only that column and not the expanded table. In that case MonthYear won't be affected and neither will Table1
https://www.sqlbi.com/articles/context-transition-and-expanded-tables/
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @Anonymous
This has mostly to do with expanded tables. Check it out here
Use the ALL on the date column only, rather than the whole table (which will affect trhe expanded table)
CALCULATE ( SUM ( Table1[Amount] ), FILTER ( ALL( Dates[Date]), Dates[Date] <= EndDate ) )
or equivalently:
CALCULATE ( SUM ( Table1[Amount] ), Dates[Date] <= EndDate )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @AlB,
Thanks for the good explanation but I still have doubt as follows:
In this scenario, expanded columns in tables: 'Dates' will be 'MonthYear[MonthYear]' and in 'Table1' will be 'MonthYear[MonthYear]' and there won't be any expanded column in table 'MonthYear'. Hence, there will be no column of 'Dates' table in 'Table1'.
So why filtering 'Dates' table filters 'Table1'? I think the question still persists.
The expanded table of Dates includes MonthYear. If you refer to the whole table dates you are referring to the expanded table and if you filter you will be filtering the MonthYear table. Then the filter will propagate from MonthYear to Table1 as usual. If you use a column of Dates, you are filtering only that column and not the expanded table. In that case MonthYear won't be affected and neither will Table1
https://www.sqlbi.com/articles/context-transition-and-expanded-tables/
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |