topic Re: Passing a Filter across Measures in DAX Commands and Tips

Passing a Filter across Measures

tobi94MTW — Tue, 04 Jul 2023 15:34:00 GMT

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(

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

Re: Passing a Filter across Measures

Sahir_Maharaj — Sun, 16 Jul 2023 19:02:10 GMT

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.

Re: Passing a Filter across Measures

tobi94MTW — Thu, 20 Jul 2023 06:41:59 GMT

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