Need help in storing previously selected values from Slicers

[Anonymous]

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

[123abc]

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.

[Anonymous]

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

[123abc]

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.

[Anonymous]

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.