Countif formula in dax

DAX DAX Calculations
1

Hi,
I have created a dashboard for manufacturing department in Power BI. It has two dates per item - Scheduled Start Date and Start Date in the same table. I have a measure which gives me the difference between schedule start and actual start date in days.
I also created another measure using if formula if it started on time using IF function. (IF SCHEDULED START DATE = STARTED DATE, TRUE, FALSE)
However, I need a count of the times it started on time. i.e. If the STARTED ON TIME = TRUE, it started on time and if it’s FALSE, it got delayed.
Can someone guide me on the best way forward to count this and display on the table?
Thanks,

Capture
Capture1358×941 151 KB

Nupur

2

@Nupur,

Here’s one way to do this:

TrueCount =

VAR vTable =
ADDCOLUMNS(
	Data,
	"@OnTime", [Started On Time]
)

RETURN
CALCULATE( 
	COUNTROWS( vTable ),
	FILTER(
		vTable,
		[@OnTime] = TRUE
	)
)

Note: you’ll have to change “Data” to the name of your corresponding fact table.

I hope this is helpful.

  • Brian
1 Like
3

Hello @Nupur

Sample data on this would really help giving you the exact solution.

Best,
Kumail Raza

4

Hi @Nupur, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

Hi @BrianJ, Thank you for providing the response.
I tried the above formula, but it gives the total count of both TRUE/FALSE. I need the count of just the TRUE ones.
Could you please help.
Thanks,
Nupur

6

@Nupur,

I suspect the problem is with your [Started on Time] measure. I think the condition should be if start date <= scheduled start date then true, otherwise false ( rather than as you have it only triggering true if the dates are equal, which may not occur within the dataset). If that doesn’t solve the problem, please provide a sample dataset/PBIX and we can work out where/why things are going sideways.

Thanks.

  • Brian
7

Actually @BrianJ your code has variable in the first argument of CALCULATE, that is giving her all the values. :slight_smile:

8

@AntrikshSharma,

Thanks - nice catch.

@Nupur - try this instead:

TrueCount =

VAR vTable =
ADDCOLUMNS(
	Data,
	"@OnTime", [Started On Time]
)

RETURN
COUNTROWS( 
	FILTER(
		vTable,
		[@OnTime] = TRUE
	)
)
  • Brian
9

Hi @BrianJ & @AntrikshSharma,

This formula worked. Awesome.

Thanks,
Nupur

10

It’s great to know that you are making progress with your query @Nupur. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!