Slicer selection dynamically returns another row

[LupinAinsworth]

Hi all, 

I am stuck on a use case. The client request is the ability to select a row in a slicer and have another row value filter in addition to the selected value. They have actual and forecast data stored in YYYYXX format, actuals are stored as YYYY00 and forcasts are stored as YYYY01-YYYY12. The ask is to only be able to select the YYYY01-YYYY12 in the slicer and be able to also see the data stored on the YYYY00 row. I cannot edit anything in the data structure (cannot create a view, calculated column, another table). So likely, the answer is dax. Any help is appreciated! 

[123abc · Accepted solution]

ertainly, you can achieve this using DAX measures in Power BI. You'll need to create a measure that considers both the selected value in the slicer and the corresponding YYYY00 row. Here's a general outline of how you could approach this:

Assuming you have a column named 'Date' and another column named 'Value' in your table, and the slicer is based on the 'Date' column:

1. Create a Measure for Forecast Values:

ForecastValue = CALCULATE(SUM(Table[Value]), RIGHT(Table[Date], 2) <> "00")

1. This measure calculates the sum of 'Value' only for rows where the last two characters of the 'Date' column are not "00". This filters out the actuals (YYYY00).

2. Create a Measure for Actual Values:

ActualValue = CALCULATE(SUM(Table[Value]), RIGHT(Table[Date], 2) = "00")

1. This measure calculates the sum of 'Value' only for rows where the last two characters of the 'Date' column are "00". This filters out the forecasts (YYYY01-YYYY12).

2. Create a Combined Measure:

ActualValue = CALCULATE(SUM(Table[Value]), RIGHT(Table[Date], 2) = "00")

1. This measure calculates the sum of 'Value' only for rows where the last two characters of the 'Date' column are "00". This filters out the forecasts (YYYY01-YYYY12).

2. Create a Combined Measure:

CombinedValue = [ActualValue] + [ForecastValue]

1. This measure sums up the values calculated for actuals and forecasts.

Now, use the 'CombinedValue' measure in your visualizations. When you select a value in the slicer corresponding to YYYY01-YYYY12, it will show the combined value of actuals (YYYY00) and forecasts.

Make sure to replace 'Table' with the actual name of your table in these measures. Adjust the column names accordingly if they are different in your dataset.

This approach doesn't modify the data structure and should provide the desired result based on your requirements.

[LupinAinsworth]

Thank you!!!