Help with if Function in DAX

[jereaallikko]

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.

Br

Jere Aallikko

[jereaallikko]

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

[ERD]

@jereaallikko ,

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

[ERD · Accepted solution]

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.

[jereaallikko]

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

[ERD]

@jereaallikko ,

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