Passing a Filter across Measures

[tobi94MTW]

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

[Sahir_Maharaj]

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]

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