topic Re: Help with if Function in DAX in DAX Commands and Tips

Help with if Function in DAX

jereaallikko — Tue, 20 Apr 2021 16:44:24 GMT

Hi all,

I am trying to show number of actions needed for my team. The idea is to create a DAX (or custom column if needed) to show number of items where some actions are needed.

Data table illustrated as below:

Here are the rules:

Main rule is: Only from items where

Status = "Open" or "Cancelled" or "Pending"

Additionally:

Information Request Status = "Waiting"
Group = "Sales"

So based on the table below, the measure should show 3 actions needed:

(On 1st row no action needed, because "Status" does not match the main rule)

Any tips for the issue? If more information is needed, don't hesitate to ask.

Jere Aallikko

Re: Help with if Function in DAX

ERD — Wed, 21 Apr 2021 08:51:13 GMT

Hi jereaallikko ,

Try this measure:

#CountItems = COUNTAX(FILTER(T, T[Status] IN {"Pending","Open","Cancelled","Closed"} && OR(T[InfStatus] = "Waiting", T[Group] = "Sales")), T[ID])

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: Help with if Function in DAX

jereaallikko — Wed, 21 Apr 2021 14:02:12 GMT

Hi ERD

That worked out well! One more question:

Is there a way to modify the measure, that if there are no actions needed, the result will show as 0? Now it shows as "blank". I am using Card as a visual.

Thanks in advance.

-Jere

Re: Help with if Function in DAX

ERD — Wed, 21 Apr 2021 14:20:41 GMT

jereaallikko ,

#CountItems = COALESCE( COUNTAX(FILTER(T, T[Status] IN {"Pending","Open","Cancelled","Closed"} && OR(T[InfStatus] = "Waiting", T[Group] = "Sales")), T[ID]), 0)

Re: Help with if Function in DAX

jereaallikko — Sun, 25 Apr 2021 18:56:26 GMT

Hi ERD

One more question regarding the issue.

How should I mofidy the measure if I want to add more conditions?

Just as an example:

All the previous conditions + action needed when Handler = Jake? or ID = 578344

I tried to add conditions to the measure as below, but it is giving error.

COALESCE( COUNTAX(FILTER(T, T[Status] IN {"Pending","Open","Cancelled","Closed"} && OR(T[InfStatus] = "Waiting", T[Group] = "Sales", T[Handler] = "Jake", T[ID] = "578344")), T[ID]), 0)

Thanks in advance,

Jere

Re: Help with if Function in DAX

ERD — Mon, 26 Apr 2021 06:36:47 GMT

jereaallikko ,

You need to use Or (||) operator in this case. https://dax.guide/op/or/