Forum Discussion

kirwanm1's avatar
kirwanm1
Frequent Visitor
9 years ago

CountIF based on grouping ID

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 ...
Need Help
vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

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's avatar
kirwanm1
Frequent Visitor
9 years ago

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's avatar
    vanessafvg
    Community Champion
    9 years ago

    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's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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's avatar
        vanessafvg
        Community Champion
        9 years ago

        makes sense to do it in powerquery MarcelBeug

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago
    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