Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter context within summarize

Dear Team,

 

I created a measure to calculate avg at the customer lvl but modified filter context, so it could work on other categories.

This measure is working as expected but only on "ch" category. The goal is to modify the filter context so values "ch" values would also populate on other categories.

I tried multiple options and the best shot was to put this logic into variable, so it could populate on other categories, but the outcome numbers are clearly wrong

 

Could you please advise?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

    To modify the filter context in your DAX measure so that the "ch" category values are applied across other categories, we need to carefully manage the calculation context. The key is to calculate the "ch" category values independently from the context of other categories. Here's how you can adjust your DAX measure to achieve this:


    VAR chQuantity =
CALCULATE(
SUM(fact[Total quantity]),
dim_product[Category] = "ch",
REMOVEFILTERS(dim_product)
)

VAR darkQuantity =
CALCULATE(
SUM(fact[Total Quantity]),
dim_product[Theme] = "dark"
)

VAR init =
SUMMARIZE(
fact,
fact[Customer Id],
"Avg",
DIVIDE(
chQuantity,
darkQuantity,
BLANK()
)
)

RETURN
AVERAGEX(init, [Avg])




    VAR chQuantity: This variable calculates the total quantity for the "ch" category across all data, removing any existing filters on 'dim_product' to ensure it isn't affected by other category filters.

    VAR darkQuantity: This calculates the total quantity for the "dark" theme. Here, we retain the natural filter context because we want the theme filter to apply only as needed.



     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

    To modify the filter context in your DAX measure so that the "ch" category values are applied across other categories, we need to carefully manage the calculation context. The key is to calculate the "ch" category values independently from the context of other categories. Here's how you can adjust your DAX measure to achieve this:


    VAR chQuantity =
CALCULATE(
SUM(fact[Total quantity]),
dim_product[Category] = "ch",
REMOVEFILTERS(dim_product)
)

VAR darkQuantity =
CALCULATE(
SUM(fact[Total Quantity]),
dim_product[Theme] = "dark"
)

VAR init =
SUMMARIZE(
fact,
fact[Customer Id],
"Avg",
DIVIDE(
chQuantity,
darkQuantity,
BLANK()
)
)

RETURN
AVERAGEX(init, [Avg])




    VAR chQuantity: This variable calculates the total quantity for the "ch" category across all data, removing any existing filters on 'dim_product' to ensure it isn't affected by other category filters.

    VAR darkQuantity: This calculates the total quantity for the "dark" theme. Here, we retain the natural filter context because we want the theme filter to apply only as needed.



     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.