Countif DAX

[akwang]

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?

[Anonymous]

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]

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.

[mede]

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

[KHorseman]

@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.

[KHorseman · Accepted solution]

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