Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Yamini_P
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

 

Please let me know, I’m in Need of Your Expertise.

 

Regards,

Yamini

4 REPLIES 4
123abc
Community Champion
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.

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

123abc
Community Champion
Community Champion

Revised Answer:

 

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.

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.