topic Re: Need help in storing previously selected values from Slicers in DAX Commands and Tips

Need help in storing previously selected values from Slicers

Anonymous — Tue, 12 Dec 2023 10:28:15 GMT

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(123 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

Re: Need help in storing previously selected values from Slicers

123abc — Tue, 12 Dec 2023 12:55:57 GMT

Here's a step-by-step guide:

Create Slicer Fields:
- Make sure you have the slicer fields (Month, A, B, C) in your dataset.
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())

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().
Create a Table Visual:
- Create a table visual with 'Month' and the newly created 'Calculation' column.
Configure the Slicers:
- Place the 'Month,' 'A,' 'B,' and 'C' slicers on your report page.
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.

Re: Need help in storing previously selected values from Slicers

Anonymous — Tue, 12 Dec 2023 13:52:58 GMT

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

Re: Need help in storing previously selected values from Slicers

123abc — Tue, 12 Dec 2023 14:51:10 GMT

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.

Re: Need help in storing previously selected values from Slicers

Anonymous — Tue, 12 Dec 2023 14:43:30 GMT

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.