Forum Discussion

b2wise's avatar
b2wise
Icon for Helper III rankHelper III
5 years ago
Solved

SELECTEDVALUE showing blank when no data

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
)
)
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    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
            )
    )

     

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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's avatar
      b2wise
      Icon for Helper III rankHelper III
      5 years ago

      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?