topic Re: Perfect Days Flag in DAX Commands and Tips

Perfect Days Flag

Yarters — Thu, 09 Feb 2023 15:36:13 GMT

I'm new to DAX and have followed a number of guides which have got me started but I'm struggling to get something to work and would appreciate some help please!

I have an 'Event' table and a 'Calendar' table and want to produce a graph that shows by month the days when a specific event happened vs days when it didn't. If the event didn't happen, we call it a 'Perfect Day'.

I have created the following measures :

No. of Events = CALCULATE ( COUNTROWS (Event) , Event [Did specific event occur?] = "Yes" )

Perfect Day =IF ( [No. of Events] > 0 , "No" , "Yes" )

This works in a table/graph with Calendar[Date] in, but unfortunately when I want to display this summarised by month, it is based on the event happening accross the month, and I want to keep the context at days and display something like this with one colour for 'Perfect' and one for 'Not Perfect':

I think I am missing something, possibly very obvious but can't seem to work it out!

Thanks in advance

Re: Perfect Days Flag

johnt75 — Thu, 09 Feb 2023 16:39:14 GMT

Try

Num perfect days = VAR SummaryTable = ADDCOLUMNS ( VALUES ( 'Calendar'[Date] ), "@num days", [No. of events] ) VAR Result = COUNTROWS ( FILTER ( SummaryTable, [@num days] = 0 ) ) RETURN Result

Re: Perfect Days Flag

Yarters — Thu, 09 Feb 2023 17:04:39 GMT

Thanks, that gives me a count - and if I create another measure like this I can stack the 2 measures in a stacked bar - thanks for your help!

Num Imperfect Days =

COUNTROWS('Calendar') - [Num perfect days]