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 Team,
Need explanation in detail why my formula is not working the way it was built and please find my PBIX file in below link. I have created a measure to calculate two weeks Rolling average of sales and its working fine when i am using WeekNum from Cal but when i bring WeekNum from Fact Value table its not calculating Rolling Average. There is relationship exist between Cal and Fact Value table still its not getting filter.
https://drive.google.com/file/d/1EnaG0vDvQmJr0k3WghtyFjtcuwECcZd-/view?usp=sharing
Solved! Go to Solution.
hi, @Anonymous
It is Row Context and Filter Context in DAX,
filter is matching to the current row context.
Best Regards,
Lin
May be because, the relation is Many to one and the Measure is based on the Cal Table. I have modified the measure by replacing cal table with Factvalue table and it is working fine.
Gap (Rolling Average 2 Weeks)_2 = VAR LastWeek = MAX ( FactValue[WeekNum]) VAR Last12Weeks = TOPN ( 2, FILTER ( ALL (FactValue[WeekNum] ), FactValue[WeekNum] <= LastWeek ), FactValue[WeekNum], DESC ) RETURN CALCULATE ( [Sales], Last12Weeks )
Ya i know if we use WeekNum from another table it will work but then same formula will not work if i place Week Num from Cal table so my question is:
I have relation between two tables in bi direction still why my filters are not working irrespective of table?
hi, @Anonymous
This relates to the filter context, In your formula,
Gap (Rolling Average 2 Weeks) = VAR LastWeek = MAX ( Cal[WeekNum]) VAR Last12Weeks = TOPN ( 2, FILTER ( ALL (Cal[WeekNum] ), Cal[WeekNum] <= LastWeek ), Cal[WeekNum], DESC ) RETURN CALCULATE ( [Sales], Last12Weeks )
you filter Cal[WeekNum] FILTER ( ALL (Cal[WeekNum] ), Cal[WeekNum] <= LastWeek )
so this measure filter context is only based on Cal[WeekNum]. for other context it won't be filtered.
If you want to every [WeekNum] could work, you need to add FactValue[WeekNum] into this measure filter context.
For example:
Gap (Rolling Average 2 Weeks) = VAR LastWeek = MAX ( Cal[WeekNum]) VAR Last12Weeks = TOPN ( 2, FILTER ( ALL (Cal[WeekNum] ), Cal[WeekNum] <= LastWeek ), Cal[WeekNum], DESC ) VAR Last12Weeks2 = TOPN ( 2, FILTER ( ALL (FactValue[WeekNum] ), FactValue[WeekNum] <= LastWeek ), FactValue[WeekNum], DESC ) RETURN CALCULATE ( [Sales], Last12Weeks,Last12Weeks2 )
Best Regards,
Lin
Thanks a lot for solution but i have below question, please clarify me?
1. My assumption is once we have relation between tables and apply the filter, it will apply to whole model right, if yes ,then why my filter WeekNum from any table not filtering another weeknum in different table. Please explain me if i am missing something.
2. If you see my below formula(1) .For the variables Last12Weeks, Last12Weeks2 i am not assigning to any column so i don't understand how its still working.
1.
CALCULATE ( [Sales], Last12Weeks,Last12Weeks2 )
2. normal way of filtering:
CALCULATE ( [Sales],
CALCULATE ( [Sales],FactValue[WeekNum] =201801)
hi, @Anonymous
It is Row Context and Filter Context in DAX,
filter is matching to the current row context.
Best Regards,
Lin
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |