Forum Discussion

jereaallikko's avatar
jereaallikko
Helper III
5 years ago
Solved

Help with if Function in DAX

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

 

 

 

Need Help DAX
Tip & Tricks
  • ERD's avatar
    ERD
    5 years ago

    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.

5 Replies

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    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's avatar
      jereaallikko
      Helper III
      5 years ago

      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's avatar
        ERD
        Community Champion
        5 years ago

        jereaallikko ,

        #CountItems = 
COALESCE(
COUNTAX(FILTER(T, T[Status] IN {"Pending","Open","Cancelled","Closed"} && OR(T[InfStatus] = "Waiting", T[Group] = "Sales")), T[ID]),
0)
  • jereaallikko's avatar
    jereaallikko
    Helper III
    5 years ago

    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