Forum Discussion
Helper IHow to use different column value if slicer has selected value
My fact table is created like this:
| Date | Carrier | Score |
| 1/1/2024 | A | 1 |
| 1/2/2024 | A | 0.98 |
| 1/3/2024 | A | 0.95 |
| 1/1/2024 | B | 0.98 |
| 1/2/2024 | B | 0.95 |
| 1/3/2024 | B | 0.9 |
I have a single dimensional table like this:
| Carrier | Carrier Hide |
| A | Carrier 1 |
| B | Carrier 2 |
There is a single slicer which uses the [Carrier] value from the dimensional table. I'm wanting to create some functionality within the report that if someone selects a [Carrier] value, then it will return that [Carrier] value for that specific row, but will return the corresponding [Carrier Hide] value for each other row.
I'm attempting to obfuscate non-selected rows with a different value if they are not selected, but leave the rows in place within the visual itself. So, if no slicer values are selected, it would return [Carrier]. If [Carrier] "A" is selected, it would return "A" for one row and "Carrier 2" for the next row, since [Carrier] "B" was not selected.
Any help/advice would be greatly appreciated!
Hi swolfe2
I noticed your message and I made some changes to the code:
First, create a table. And use the columns of the new table as slicers.
Slicer Table = SELECTCOLUMNS('Carrier', "carrier", 'Carrier'[Carrier])Create a measure.
Selected Carrier = VAR SelectedCarrier = SELECTEDVALUE('Slicer Table'[carrier]) RETURN IF( SELECTEDVALUE('Fact'[Carrier]) = SelectedCarrier, SELECTEDVALUE('Fact'[Carrier]), LOOKUPVALUE('Carrier'[Carrier Hide], 'Carrier'[Carrier], SELECTEDVALUE('Fact'[Carrier])) )Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi swolfe2
Try the following code:
Selected Carrier = VAR SelectedCarrier = SELECTEDVALUE('Dimensional Table'[Carrier]) RETURN IF( ISFILTERED('Dimensional Table'[Carrier]) && SELECTEDVALUE('Fact Table'[Carrier]) = SelectedCarrier, SELECTEDVALUE('Fact Table'[Carrier]), LOOKUPVALUE('Dimensional Table'[Carrier Hide], 'Dimensional Table'[Carrier], SELECTEDVALUE('Fact Table'[Carrier])) )Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
swolfe2Thanks so much for looking into this, Anonymous. Unfortunately, this solution doesn't quite do what I'm expecting. As an example, when there is no slicer slection, nothing is returned:
Filtering to Carrier A only has rows for Carrier A:
I have added the .pbix file to my Google Drive, and you can download it to experiment. Again, I do really appreciate the effort!
Hi swolfe2
I noticed your message and I made some changes to the code:
First, create a table. And use the columns of the new table as slicers.
Slicer Table = SELECTCOLUMNS('Carrier', "carrier", 'Carrier'[Carrier])Create a measure.
Selected Carrier = VAR SelectedCarrier = SELECTEDVALUE('Slicer Table'[carrier]) RETURN IF( SELECTEDVALUE('Fact'[Carrier]) = SelectedCarrier, SELECTEDVALUE('Fact'[Carrier]), LOOKUPVALUE('Carrier'[Carrier Hide], 'Carrier'[Carrier], SELECTEDVALUE('Fact'[Carrier])) )Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- swolfe2
This did it! Thanks so much for the detailed solution and .pbix file.