Forum Discussion

tobi94MTW's avatar
tobi94MTW
Frequent Visitor
3 years ago

Passing a Filter across Measures

Hi everyone,

 

I am trying to solve the following issue:
I have an aggregate measure [Summary] that calls various underlying measures [Components]. In these [Components]-Measures I have a dynamic parameter choice that selects appropriate factors from a parameter list for different years based on the filter context from the [Summary]-Measure. 
My goal is to be able to play around with: "What if I use the Factors from year A for year B?" 

 

To this purpose, I replicated the [Summary]-Measure and now want to "fix" the year component based on some variable (that I can use in a slicer) to be applied for all years. To do so, I use CROSSFILTER to ignore the relationship (that allows the dynamic measure to work and I rely upon) and now only need to somehow tell PowerBI to use a specific year-value when filtering the parameter list that is called in the underlying [Components]-Measures.   


My [Summary]-measure I have tried goes like:   

 

 CALCULATE(
CALCULATE(
VAR PRODUCT_ = if(ISFILTERED('PRODUCTS'[Product]),FILTERS('Products'[Product]))
RETURN
IF(PRODUCT_ = "A",SUMX('STORE',[SALES_A]),
IF(PRODUCT_ = "B",SUMX('STORE',[SALES_B]),
    IF(ISFILTERED('PRODUCTS'[Product]) = false, SUMX('STORE', [SALES_A])+SUMX('STORE',[SALES_B])
))))
, CROSSFILTER('Sales'[Year], 'Factors'[Year], None)
, KEEPFILTERS('Factors'[Year] = 2023)
)
 
The CROSSFILTER-part does what it is supposed to do, yet the KEEPFILTERS-part does not work. The goal is to select a specific row in the parameter list, in the best case not hard coded as here but based on some variable like 'Year'[Year] that can be filtered dynamically in a visual.
For completion: In the underlying COMPONENTS-measures I call columns from the 'Factors' table with a MAX-function. So if I do not select a specific year value it would always take the highest possible value across all years, instead of the ones from the same year.
 
Thanks a lot for any suggestions and tips





2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    Hello tobi94MTW,

     

    1. Create a measure that retrieves the selected year value from the slicer

    SelectedYear = SELECTEDVALUE('Year'[Year])

    2. Modify your [Summary] measure to use the SelectedYear measure

    CALCULATE(
    CALCULATE(
        VAR PRODUCT_ = IF(ISFILTERED('PRODUCTS'[Product]), FILTERS('Products'[Product]))
        RETURN
            IF(
                PRODUCT_ = "A",
                SUMX('STORE', [SALES_A]),
                IF(
                    PRODUCT_ = "B",
                    SUMX('STORE', [SALES_B]),
                    IF(
                        ISFILTERED('PRODUCTS'[Product]) = FALSE,
                        SUMX('STORE', [SALES_A]) + SUMX('STORE', [SALES_B])
                    )
                )
            )
    ),
    CROSSFILTER('Sales'[Year], 'Factors'[Year], NONE),
    KEEPFILTERS('Factors'[Year] = [SelectedYear])
)

    Should you need further assistance please don't hesitate to reach out to me.

    • tobi94MTW's avatar
      tobi94MTW
      Frequent Visitor
      3 years ago

      Dear Sahir,
      thanks a lot for the great advice, it got me there almost but not the full way yet.
      In my Summary Measure I know get the warning

      "A function of type 'PLACEHOLDER' was used in a True/False expression that serves as a table filter expression. This is not allowed."
      I did all steps as suggested such that I have a measure that uses selectedvalue on 'Year[Year]', entered that in my [Summary]-Measure and inserted a slicer on the page. My understanding is that this warning is because of using the "SelectedYear" Measure within CALCULATE, though I would have expected that this is allowed within a Filter-type function.
      Do you have any guess on how I can circumvent my problem?

      Thanks a lot and best

      Tobi