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 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

 

Show and Tell

5 Replies

  • rocky09's avatar
    rocky09
    Solution Sage
    7 years ago

    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'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