CountIF based on grouping ID

[kirwanm1]

Hey Everyone, 

So I have the following table structure as a series of events with each line representing a row.

Grouping ID - Event

Event A - Trigger 1
Event A - Trigger 2
Event A - Trigger 3
Event B - Trigger 1
Event B - Trigger 3
Event C - Trigger 1
Event C - Trigger 3
Event D - Trigger 1
Event D - Trigger 2
Event D - Trigger 3
Event E - Trigger 1
Event E - Trigger 2
Event E - Trigger 3

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 & Trigger 3 are in events that share the same aggregate id. Event A, Event D & Event E

countingfunction(Trigger1. Trigger 3) returns 5 as Trigger 1 and Trigger 3 are in all three events.

Does anyone have some advice on how to go about this in Power BI?

Thanks!

[v-huizhn-msft]

Hi @kirwanm1,

Please try to resolve your issue based on @vanessafvg and @MarcelBeug posted. If you have any other problem, plesae feel free to ask.

Kind Regards,
Angelia

[vanessafvg]

@kirwanm1

are you saying you want to calculate the rows where these 2 events are present?

measure = calculate(countrows(table), Event = "Trigger 1" || Event = "Trigger 2")

[kirwanm1]

No Sorry, for the confusion, they are two seperate columns in the above example.

I want to group all the event ids together, and calculate how many events where the event rows contain two inputs.

[vanessafvg]

@kirwanm1  well if you using a visual  where you place the event  on it and then have a measure like

measure = distinctcount(column)  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

unless you want to do a count of all events, are the triggers duplicated per event?  and if so are you wanting to know that?

[kirwanm1]

No thats not it. I need to group based on the first column. 

ID          Event

1            Yes

1            No

1            Maybe

2            Yes

2            Yes

2            No

3            Maybe

3            Yes

3            No

So 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 & Maybe, which would be 2 as id no.2 contains no maybes. Does this make sense?

[vanessafvg]

No/Maybe =
COUNTROWS (
    INTERSECT (
        CALCULATETABLE (
            DISTINCT ( Table[id] ),
            'Table'[Event] = "No"
        ),
        CALCULATETABLE (
            DISTINCT (Table[id] ),
            Table'[Event] = "Maybe"
        )
    )
)

 you can also give this a go @kirwanm1

[vanessafvg]

@kirwanm1

it does make sense yes, it sounds like you would get the result for each i.e maybe and then no and then merge where there is only both,i will have to think deeper on how to do it

[MarcelBeug]

This would be my suggestion for the countingfunction:

(Trigger1 as text, Trigger2 as text) as number =>
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]

makes sense to do it in powerquery @MarcelBeug