Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Switch DAX statement - If FALSE, return all rows

Hello,
 
I'm trying to add a condition to my report that states if not Action type (filter) is applied, return all rows.
Currently, I have a measure which recommends a specific action based on the amount of employee leave accrued.
This is a simple IF statement that defines the categories of [ActionMeasure]
 
Then I have a filter which allows the user to view those employees which are categorised against those actions.
 

Filter Status =

 

 Var CurrentSelection = SELECTEDVALUE(ActOrd[Action])
 Var CurrentAction = [ActionMeasure]

 

Return
Switch(
     True(),
     CurrentSelection = "Monitor" && CurrentAction = "Monitor",1,
     CurrentSelection = "Start Conversation" && CurrentAction = "Start Conversation",1,
     CurrentSelection = "Request Leave Plan" && CurrentAction = "Request Leave Plan",1,
     CurrentSelection = "May be directed to take leave" && CurrentAction = "May be directed to take leave",1,
     0
)

This is then applied as a filter on the visual.
 
However, I want the filter to behave as such. If there is no filter selected (i.e. return 0) then no filter is applied and the user can see all records.
 
Thanks

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    how is your measure [ActionMeasure] defined?

    could you make a screenshot indicating what do you mean by "the user can see all records"?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    Thanks for your response FreemanZ !

    Action measure is defined as:

    ActionMeasure =
        switch(
            True(),
            Sum('Employee Leave Data'[Accrued Weeks]) >= 4
        && Sum('Employee Leave Data'[Accrued Weeks]) <= 5.99,
        "Start Conversation",
            Sum('Employee Leave Data'[Accrued Weeks]) >= 6
        && Sum('Employee Leave Data'[Accrued Weeks]) <= 7.99,
        "Request Leave Plan",
            Sum('Employee Leave Data'[Accrued Weeks]) >8,
        "May be directed to take leave",
            SUMX('Employee Leave Data', 'Employee Leave Data'[Accrued Leave (Hrs)]) >= 149
            && SUMX('Employee Leave Data', 'Employee Leave Data'[Accrued Leave (Hrs)]) <= 160,
            "Monitor",
            "No Action Required"
        )
     
    As to your second question, when I choose a filter it only returns those rows selected

     

    With no filter applied, the visual breaks


    however I would like the visual to return all results when no filter is applied, such as the below example

     

    Thanks!

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi Anonymous

      try like:

      Filter Status =
      Var CurrentSelection = SELECTEDVALUE(ActOrd[Action])
      Var CurrentAction = [ActionMeasure]
      Return
      Switch(
           True(),
           CurrentSelection = "Monitor" && CurrentAction = "Monitor",1,
           CurrentSelection = "Start Conversation" && CurrentAction = "Start Conversation",1,
           CurrentSelection = "Request Leave Plan" && CurrentAction = "Request Leave Plan",1,
           CurrentSelection = "May be directed to take leave" && CurrentAction = "May be directed to take leave",1,
           CurrentAction=BLANK(), 1,
           0
      )