Forum Discussion
Countrows per multiple columns
Hi Team,
I need a measure or column for getting the below.
I want to count per meetingid, per participant, per Attribute, if Value is greater than 5 and this 5 appears morethan 3 times then give 1 else 0.
| MeetingInstanceID | ParticipantsName | Attribute2 | Value | if >5? | Latency? | |
| 1 | fd | Latency | 5 | 0 | 1 | <-- IF(COUNTIF(E2:E7,"1")>3,1,0) |
| 1 | we | Latency | 6 | 1 | ||
| 1 | rr | Latency | 9 | 1 | ||
| 1 | ee | Latency | 8 | 1 | ||
| 1 | tt | Latency | 6 | 1 | ||
| 1 | Latency | 9 | 1 | |||
| 2 | yy | Latency | 2 | 0 | 1 | <-- IF(COUNTIF(E8:E15,"1")>3,1,0) |
| 2 | re | Latency | 9 | 1 | ||
| 2 | gr | Latency | 1 | 0 | ||
| 2 | rrf | Latency | 8 | 1 | ||
| 2 | fdd | Latency | 8 | 1 | ||
| 2 | ffa | Latency | 4 | 0 | ||
| 2 | fas | Latency | 2 | 0 | ||
| 2 | asd | Latency | 7 | 1 |
the solution for this question is posted at the above link. Thanks for everyone helped.
8 Replies
the solution for this question is posted at the above link. Thanks for everyone helped.
LP2803 , Create a new column like
if(sumx(Filter(table, [MeetingInstanceID] = earlier([MeetingInstanceID])),if([Value]>5,1,0))>3,1,0)
amitchandak Thanks amit, But I also need to consider ParticipantID and the Attribute columns as well.
In the example above, i couldnt show more participantID with same meetingID and attribute but in actual data i have duplicates in meetingid, participants & attribute.
LP2803 , Check if this can work
if(sumx(Filter(table, [MeetingInstanceID] = earlier([MeetingInstanceID]) && [ParticipantsName] = earlier([ParticipantsName])),if([Value]>5,1,0))>3,1,0)
amitchandak thanks.
I tried, but i get error "Cannot find name"
check = if(sumx(Filter('Sheet1 (2)', 'Sheet1 (2)'[Column1.meetingInstanceId] = earlier([Column1.meetingInstanceId]) && 'Sheet1 (2)'[Column1.participantSessionId] = earlier([Column1.participantSessionId])),if([Value]>5,1,0))>3,1,0)Any idea please...
Hi LP2803 ,
Or create a measure like this.
Measure = VAR __sum = SUMX( FILTER( ALLSELECTED(Sheet1), [MeetingInstanceID] = MAX([MeetingInstanceID]) && [Attribute2] = MAX([Attribute2]) && [ParticipantsName] = MAX([ParticipantsName])&& [if >5?] = 1 ), [if >5?] ) RETURN IF( __sum > 3, 1, 0 )Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lionel-msft Hi lionel, thanks for the response.
I have multiple attributes, like Latency, Packetloss, framerate etc which i need to include.
For example count the participant in a meeting who has got Packetloss of morethan 5 for 3 times.
Likewise count the participant in a meeting who has got Latency of morethan 400 for 3 times.
Any idea how I can do this in one query without create a extra columns
Hi LP2803 ，
Thank you for your solution!
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
