CODEWITHSUNDEEP
powerbidax
oilers128
oilers128

Reputation: 192

DAX using SWITCH and SELECTEDVALUE for an output based on a slicer selection

I have a column that shows number of hours aging ("AgingHours") for "Orders". I want to create another column called "Aged" that will = 1 if the "AgingHours" value is greater than the selected value in the slicer, otherwise 0. The user is able to select either "1 Hr", "2 Hrs", or "3 Hrs" from the slicer.

For example:

Created slicer called AgingDate[HourDay]. User selects "2 Hrs" from the slicer (which would mean, anything in the "AgingHours" column that is >= 2 would output a 1 in the "Aged" column. The output in the aged column below would be:

Order AgingHours Aged
A          1       0
B          2       1

I tried making a calculated column utilizing an IF statement based on the selection, but every value is showing 0.

Aged = 
SWITCH (
    SELECTEDVALUE( AgingDate[HourDay] ),
    "1 Hr", IF ( [AgingHours] >= 1, 1, 0 ),
    "2 Hrs", IF ( [AgingHours] >= 2, 1, 0 ),
    "3 Hrs", IF ( [AgingHours] >= 3, 1, 0 ),
     0
    )

Upvotes: 1

Views: 11922

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

Calculated columns are only evaluated once when the data is loaded. Therefore, they cannot respond to any slicers or filtering on your report page.

In particular, SELECTEDVALUE( AgingDate[HourDay] ) is returning a blank value (since it doesn't know what single value you have selected), which means your SWITCH is taking the else value of 0.

If you use the same code as a measure, it looks like it should work OK as long as you use the table name as well when you refer to [AgingHours].

Upvotes: 3

Related Questions