Forum Discussion

Yarters's avatar
Yarters
Frequent Visitor
3 years ago
Solved

Perfect Days Flag

Hi 

 

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

  • johnt75's avatar
    johnt75
    3 years ago

    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

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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
  • Yarters's avatar
    Yarters
    Frequent Visitor
    3 years ago

    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]