Help with measure to count where an event occured on a date

[Philandcas]

Hi experts,

I'm looking for some assistance in creating a measure that checks if one event or more occured on a particular date if it did then count as 1 ifelse 0.

I can easily count the total number of events that occur on a particular day but I'm stumped on how I create a measure to count only 1 where there may be many.

Amny help would be great.

Phil

[v-yiruan-msft]

Hi @Philandcas ,

You can create a measure as below to get it, please find the details in the attachment.

Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Event] ),
        FILTER ( 'Table', 'Table'[Date] = _seldate )
    )
RETURN
    IF ( _count >= 1, 1, 0 )

Best Regards

[Philandcas]

Hi @v-yiruan-msft ,

Thank you for the reply, your calculation does exactly what I was hoping to do.
I do have one final question though, If I want to count the number days where there was an event how do I add that to the DAX?

What I need to be able to calculate is the number of dates where there was an event on, and the number dates where there wasn't an event on.

Kind regards

Phil

[v-yiruan-msft · Accepted solution]

Hi @Philandcas ,

You can create another measure as below to get it, please find the details in the attachment.

Number of dates which have events = SUMX(VALUES('Date'[Date]),[Measure])

Best Regards

[NikhilChenna]

Hi @Philandcas ,

Consider this below example, 

Event    Date

A           11/29/2022

A           11/29/2022

B           11/29/2022

C           11/29/2022

So suppose if  condition is that on 29th Nov 22, A event should occur.

Then you have to create a calculated column in you table as ,

Calculated Column = IF('table1'[Date]="11/29/2022" && 'table1'[Event]="A",1,0)

So you'll get the output as below,

Event    Date                Eventsoccured

A           11/29/2022     1

A           11/29/2022     1

B           11/29/2022     0

C           11/29/2022     0

For getting the distinct count for this you have to use a calculated measure, 

Distinctcount =
CALCULATE(
DISTINCTCOUNT ('table1'[Eventsoccured]),
FILTER('table1','table1'[Eventsoccured]=1)
)

This will solve your issue.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!