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