Forum Discussion

akwang's avatar
akwang
Advocate II
8 years ago
Solved

Countif DAX

Hi I have a table with a column for order status which could either be "on time" or "delayed". I want to create a MEASURE for Total Orders delayed that will tell me how much orders were delayed. In excel i would just use the CountIF Function, but DAX doesnt have this.  Can anyone advise on how i can get this?

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    CALCULATE and FILTER are the first two things you need to learn about DAX.

     

    Measure = CALCULATE(
	COUNT(TableName[Column That Identifies Unique Orders]),
	FILTER(
		TableName,
		TableName[Delay Status] = "On Time" ||
		TableName[Delay Status] = "Delayed"
	)
)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    CALCULATE and FILTER are the first two things you need to learn about DAX.

     

    Measure = CALCULATE(
	COUNT(TableName[Column That Identifies Unique Orders]),
	FILTER(
		TableName,
		TableName[Delay Status] = "On Time" ||
		TableName[Delay Status] = "Delayed"
	)
)
  • mede's avatar
    mede
    Resolver I
    8 years ago

    You need to familiarize yourself with DAX logic:

     

    This will give you count of On Time orders: 

    Count of On Time Orders = CALCULATE(DISTINCTCOUNT(TableName[order_id]),TableName[Delay Status] = "On Time" )

    in plain english: Calculate the distinctcount of order_ids where delay status is "on time"

     

    This will give you count of Fulfilled AND On Time orders

    Count of Fulfilled On Time Orders = CALCULATE(DISTINCTCOUNT(TableName[order_id]),TableName[Delay Status] = "On Time", TableName[Short Ship Status] = "Fulfilled")
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      medecareful with that shortcut syntax. It includes an implicit ALL() clause that can cause unexpected results if you don't know what you're doing with it. Can be dangerous to suggest to beginners. I prefer to always use explicit FILTER() statements to avoid this.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi i have a Date set where can see 4 column of Ans1,ans2,ans3,ans4
    i want to know in each column how many time 3 have got

    if i would try same in excel so will do countif(seletshell,3)

    Thanks in advance

    akwang wrote:

    Hi I have a table with a column for order status which could either be "on time" or "delayed". I want to create a MEASURE for Total Orders delayed that will tell me how much orders were delayed. In excel i would just use the CountIF Function, but DAX doesnt have this.  Can anyone advise on how i can get this?

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      In the Query Editor, select all column other than the 4 answer columns and click on "Unpivot other columns".  Now write this measure

      =CALCULATE(COUNTROWS(Data),Data[Value]<=3)

      Hope this helps.