cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Need help in storing previously selected values from Slicers

Hi,

I have slicers(Month, A, B, C where Month has months and A,B,C has numeric values), and a table visual with a calculation (A*B*C)as below,

So my requirement is when Jan is selected and lets say A=1, B=2, C=3 then the visual should display as below:

 Month Calculation Jan 6(1*2*3 as per calculation) Feb Mar

Then when I select March and A=4, B=5, C=2 then visual should display as below:

 Month Calculation Jan 6 Feb Mar 40

Then when I select Feb and A=10, B=4, C=3 then visual should display as below:

 Month Calculation Jan 6 Feb 120 Mar 40

Regards,

Yamini

4 REPLIES 4
Community Champion

Here's a step-by-step guide:

1. Create Slicer Fields:

• Make sure you have the slicer fields (Month, A, B, C) in your dataset.
2. Create a Calculated Column for the Calculation:

• In the table where you have your data, create a new calculated column. Let's call it "Calculation."

• Use the following DAX formula for the calculated column:

Calculation = IF(ISFILTERED('Table'[Month]), 'Table'[A] * 'Table'[B] * 'Table'[C], BLANK())

1. This formula checks if the 'Month' slicer is filtered. If it is, it calculates the result based on the selected A, B, and C values. If not, it returns BLANK().

2. Create a Table Visual:

• Create a table visual with 'Month' and the newly created 'Calculation' column.
3. Configure the Slicers:

• Place the 'Month,' 'A,' 'B,' and 'C' slicers on your report page.
4. Test the Solution:

• Now, when you select values in the slicers, the 'Calculation' column in the table visual should dynamically update based on the selected values.

This approach uses the ISFILTERED function to check whether the 'Month' slicer is being used, and if it is, it performs the calculation; otherwise, it returns BLANK(). The table visual will then display the calculated values dynamically based on your slicer selections.

Remember to replace 'Table' with the actual name of your table in the DAX formulas. Adjust the formulas based on your actual field names and table structure.

Regular Visitor

Month, A, B, C are in different table
Can you suggest anything?

Community Champion

Certainly! If Month, A, B, and C are in different tables, you'll need to modify the DAX code to consider relationships between these tables. Assuming there is a relationship between the tables, you can use RELATED or RELATEDTABLE functions to fetch the corresponding values. Here's an example:

Calculation =
VAR SelectedMonth = SELECTEDVALUE('MonthTable'[Month])
VAR SelectedA = SELECTEDVALUE('ATable'[A])
VAR SelectedB = SELECTEDVALUE('BTable'[B])
VAR SelectedC = SELECTEDVALUE('CTable'[C])

RETURN
IF (
NOT ISBLANK(SelectedMonth),
RELATED('ATable'[A]) * RELATED('BTable'[B]) * RELATED('CTable'[C]),
BLANK()
)

In this example, replace 'MonthTable', 'ATable', 'BTable', and 'CTable' with the actual names of your tables. This assumes there is a relationship between the tables based on the Month column.

Make sure to establish relationships between the tables in the Power BI model. You can do this by going to the "Model" view and dragging the corresponding fields to create relationships.

Adjust the code based on your actual table and column names and the relationships in your model. This approach ensures that the calculations are performed based on the related values from different tables.

Regular Visitor

Cant establish a relation as A, B, C are range of numbers which is manually created in power bi desktop and A, B, C are displayed as slider values.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors