Need Explanation

[Anonymous]

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

[rocky09]

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 )

[Anonymous]

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?

[v-lili6-msft]

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

[Anonymous]

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)

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

It is Row Context and Filter Context in DAX,

filter is matching to the current row context.

Best Regards,

Lin