topic Re: Switch DAX statement - If FALSE, return all rows in DAX Commands and Tips

Switch DAX statement - If FALSE, return all rows

Anonymous — Sat, 04 Mar 2023 04:05:42 GMT

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,

)

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

Re: Switch DAX statement - If FALSE, return all rows

FreemanZ — Sat, 04 Mar 2023 04:21:26 GMT

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"?

Re: Switch DAX statement - If FALSE, return all rows

Anonymous — Sat, 04 Mar 2023 05:12:49 GMT

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!

Re: Switch DAX statement - If FALSE, return all rows

FreemanZ — Sat, 04 Mar 2023 05:26:15 GMT

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,

)