SELECTEDVALUE showing blank when no data

[b2wise]

Hi,

I'm counting picks in the warehouse in the last hour and measuring against targets. Everything works fine except that when there are 0 lines picked the target number turns blank. 

I am using one "Targets" fromula for all of my gauges and filtering each gauge to one zone of the warehouse e.g. HRZU and VLM. Using SELECTEDVALUE shows the correct target number for the selected zone. It all works great until there are 0 picks. How do I get that target number to stay despite 0 picks?

Here is the formula that I'm using:

Targets =

// Targets for all zones, the target depends on the zone filter of the visual and the time of day. The time of day is 2.75 which is 1/1/1900 18:00.

VAR Selection =

SELECTEDVALUE ( 'Order Picks'[Zone ID] )

RETURN

SWITCH (

TRUE (),

Selection = "CFLO2",

SWITCH (

TRUE (),

MAX ( 'Current Date Time'[Time] ) < 2.75, 34,

MAX ( 'Current Date Time'[Time] ) >= 2.75, 24

),

Selection = "CFLOW",

SWITCH (

TRUE (),

MAX ( 'Current Date Time'[Time] ) < 2.75, 75,

MAX ( 'Current Date Time'[Time] ) >= 2.75, 56

),

Selection = "CSGDS",

SWITCH (

TRUE (),

MAX ( 'Current Date Time'[Time] ) < 2.75, 45,

MAX ( 'Current Date Time'[Time] ) >= 2.75, 13

),

Selection = "HRZL",

SWITCH (

TRUE (),

MAX ( 'Current Date Time'[Time] ) < 2.75, 60,

MAX ( 'Current Date Time'[Time] ) >= 2.75, 31

),

Selection = "HRZU",

SWITCH (

TRUE (),

MAX ( 'Current Date Time'[Time] ) < 2.75, 54,

MAX ( 'Current Date Time'[Time] ) >= 2.75, 28

),

Selection = "VLM",

SWITCH (

TRUE (),

MAX ( 'Current Date Time'[Time] ) < 2.75, 44,

MAX ( 'Current Date Time'[Time] ) >= 2.75, 16

)

)

[jdbuchanan71 · Accepted solution]

@b2wise 

I'm thinking it's caused by the MAX ( 'Current Date Time'[Time] ) returning BLANK.  Maybe try it like this (I reorganized the switches a bit to make it easier for me to understand).  The _Time VAR give a 0 if the MAX ( 'Current Date Time'[Time] ) is BLANK.

Targets =
// Targets for all zones, the target depends on the zone filter of the visual and the time of day. The time of day is 2.75 which is 1/1/1900 18:00.
VAR _Selection =
    SELECTEDVALUE ( 'Order Picks'[Zone ID] )
VAR _MaxTime =
    MAX ( 'Current Date Time'[Time] )
VAR _Time =
    IF ( ISBLANK ( _MaxTime ), 0, _MaxTime )
RETURN
    SWITCH (
        TRUE (),
        _Time < 2.75,
            SWITCH (
                _Selection,
                "CFLO2", 34,
                "CFLOW", 75,
                "CSGDS", 45,
                "HRZL", 60,
                "HRZU", 54,
                "VLM", 44
            ),
        _Time >= 2.75,
            SWITCH (
                _Selection,
                "CFLO2", 24,
                "CFLOW", 25,
                "CSGDS", 13,
                "HRZL", 31,
                "HRZU", 28,
                "VLM", 16
            )
    )

[mahoneypat]

Not sure this is what you mean, but you can use the optional alternateresult term of SELECTEDVALUE to have a default value when nothing is selected from the slicer

VAR Selection =

SELECTEDVALUE ( 'Order Picks'[Zone ID], "CFL02" )

RETURN

SWITCH (

TRUE (),

Selection = "CFLO2",

Pat

[b2wise]

@jdbuchanan71 @mahoneypat  Thanks I reorganized the switches as well. The issue here is that I'm counting picks in the warehouse in the last hour, sometimes there were 0 picks in a certain zone and SELECTEDVALUE doesn't work because there is nothing to select (despite a filter on the visual for a that zone). @mahoneypat  I made one big formula for all the zones so the alternate result is not an option here because I would want a different number based on each zone.

Is there a work around for this issue?