topic How to use different column value if slicer has selected value in DAX Commands and Tips

How to use different column value if slicer has selected value

swolfe2 — Tue, 16 Jul 2024 18:50:40 GMT

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!

Re: How to use different column value if slicer has selected value

Anonymous — Wed, 17 Jul 2024 08:53:22 GMT

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.

Re: How to use different column value if slicer has selected value

swolfe2 — Wed, 17 Jul 2024 15:48:43 GMT

Thanks 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!

Re: How to use different column value if slicer has selected value

Anonymous — Fri, 26 Jul 2024 08:42:22 GMT

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.

Re: How to use different column value if slicer has selected value

swolfe2 — Fri, 26 Jul 2024 11:26:09 GMT

This did it! Thanks so much for the detailed solution and .pbix file.