How to use different column value if slicer has selected value

[swolfe2]

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!

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[swolfe2]

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!