Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
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