Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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" ) )
Proud to be a Super User!
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?
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.
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")
@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.
Proud to be a Super User!
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" ) )
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |