Forum Discussion
SELECTEDVALUE Multiple Slicers from One Column into One Visual
Greetings.
I have one table that contains (among other things) a Ledger Type and Dollars. Example:
| Ledger Type | Dollars |
| 1 | 500 |
| 2 | 500 |
| 3 | 500 |
| 2 | 400 |
| 3 | 400 |
| 1 | 100 |
What I want is to have 2 seperate slicers each having the field as Ledger Type. A user should be able to select a Type from the first slicer and have the dollar values displayed in a visual. User should also be able to select a value from the second slicer and have the dollar values associated with that Type displayed in the same visual. Essentially, the ability to select 2 Ledger Types and have the dollars displayed in a single visual so they can be compared.
I've tried a few things and keep striking out, but I think I'm close. Here's where I'm at:
1. Created measures for each Ledger Type, Selection 1 & 2
- 1Select1 = CALCULATE(SUM('Table'[Dollars]),'Table'[LedgerType]=1
- 2Selec1 = CALCULATE(SUM('Table'[Dollars]),'Table'[LedgerType]=1
- 1Select2 = CALCULATE(SUM('Table'[Dollars]),'Table'[LedgerType]=2
- 2Select2 = CALCULATE(SUM('Table'[Dollars]),'Table'[LedgerType]=2
etc.
2. Created measures for the selected values
- Selected1 = SWITCH(TRUE(),SELECTEDVALUE('Table'[LedgerType]=1,'MeasureTable'[1Select1],SELECTEDVALUE('Table'[LedgerType]=2,'MeasureTable'[1Select2],SELECTEDVALUE('Table'[LedgerType]=3,'MeasureTable'[1Select3]
- Selected2 = SWITCH(TRUE(),SELECTEDVALUE('Table'[LedgerType]=1,'MeasureTable'[2Select1],SELECTEDVALUE('Table'[LedgerType]=2,'MeasureTable'[2Select2],SELECTEDVALUE('Table'[LedgerType]=3,'MeasureTable'[2Select3]
Both the Selected1 and Selected2 measures are then pulled into the visual. The problem is: I can select a Ledger Type from Slicer 1 and the data displayed in the visual filters to that Ledger Type for both of the "SelectedX" measures. If I select a Ledger Type in Slicer 2 the visual goes blank completey. I've tried creating a duplicate of the Ledger Type column (in various ways) and pointing the Select2 logic at that, same results.
Example using the table above:
Slicer 1 Selection = 1
Slicer 2 Selection = 2
In the visual (it's a column chart) I would expect to see one column for selection 1, which would equal 600 and a second column for selection 2, which would equal 900.
Instead, column 1 and column 2 will show the same figures from whichever Slicer had a selection made first. So if I select value 1 in Slicer 1 then both columns in the visual show 600. If I select an option from the second Slicer the visual goes blank.
How can I set this up so a user can select a value in slicer 1, another value in slicer 2 and have those 2 values displayed in a visual?
Any help is greatly appreciated.
1 Reply
Hi, Anonymous
I think you will need to create some new tables to use in the slicers. This way the selection in one won't affect the selection in the other.
I made two new tables, one for each slicer, that contain a column of all the possible ledger values. If you don't have many you could manually enter these or you could use power query to get the unique list from your table.
let Source = (your source here), #"Removed Other Columns" = Table.SelectColumns(Source,{"Ledger Type"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns") in #"Removed Duplicates"
I haven't created any relationships here, if Power BI adds them in automatically you will need to delete them.
Now you need to create two copies of each measure, which will calculate independently of each other, just depending on the selection in the relevant slicer. , in the general format
Ledger A Measure = CALCULATE([Measure], TREATAS('Ledger A','Table'[Ledger Type]))
Ledger B Measure = CALCULATE([Measure], TREATAS('Ledger B','Table'[Ledger Type]))
Hope that helps, any problems let me know.
