Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need Explanation

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...
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-lili6-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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)