CountIF based on grouping ID

Question

Hey Everyone,&nbsp;So I have the following table structure as a series of events with each line representing a row.Grouping&nbsp;ID - EventEvent A - Trigger 1Event A - Trigger 2Event A - Trigger 3Event B - Trigger 1Event B - Trigger 3Event C - Trigger 1Event C - Trigger 3Event D - Trigger 1Event D - Trigger 2Event D - Trigger 3Event E - Trigger 1Event E - Trigger 2Event E - Trigger 3&nbsp;I'm struggling to find a function that will allow to count the number of events that contain two triggers.I.e. countingfunction(Trigger 2, Trigger 3) returns 3.Because Trigger 2 &amp; Trigger 3 are in events that share the same aggregate id. Event A, Event D &amp; Event Ecountingfunction(Trigger1. Trigger 3) returns 5 as Trigger 1 and Trigger 3 are in all three events.Does anyone have some advice on how&nbsp;to go about this in Power BI?Thanks!

marcelbeug · Answer

This would be my suggestion for the countingfunction:&nbsp;(Trigger1 as text, Trigger2 as text) as number =&gt;
let
    Selection1 = Table.SelectRows(Table1, each [Event] = Trigger1),
    Groups1 = Selection1[#"Grouping ID"],
    Selection2 = Table.SelectRows(Table1, each [Event] = Trigger2),
    Groups2 = Selection2[#"Grouping ID"],
    Count = List.Count(List.Intersect({Groups1,Groups2}))
in
    Count

vanessafvg · Answer

kirwanm1&nbsp;are you saying you want to calculate the rows where these 2 events are present?&nbsp;&nbsp;measure = calculate(countrows(table), Event = "Trigger 1" || Event = "Trigger 2")

kirwanm1 · Answer

No Sorry, for the confusion, they are two seperate columns in the above example.&nbsp;I want to group all the event ids together, and calculate how many events where the event rows contain two inputs.

vanessafvg · Answer

kirwanm1&nbsp; well if you using a visual&nbsp;&nbsp;where you place the event &nbsp;on it and then have a measure like&nbsp;measure = distinctcount(column) &nbsp;on the trigger column that should give you a distinct count of triggers per event, when you place those 2 fields you will get the event with the unique count of triggers&nbsp;unless you want to do a count of all events, are the triggers duplicated per event? &nbsp;and if so are you wanting to know that?

kirwanm1 · Answer

No thats not it. I need to group based on the first column.&nbsp;ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Event1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;No1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Maybe2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;No3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Maybe3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yes3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NoSo that is the table above. I need a distinctcount of the ID above which would be three 3. Filtered to only include if the Event contains a No &amp; Maybe, which would be 2 as id no.2 contains no maybes. Does this make sense?

Forum Discussion

CountIF based on grouping ID

9 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Index Countif

Community User Group Experience

Countifs

DAX - countif after grouping