Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LupinAinsworth
Frequent Visitor

Slicer selection dynamically returns another row

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! 

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

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.

View solution in original post

2 REPLIES 2
123abc
Community Champion
Community Champion

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.

Thank you!!!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors