Countrows per multiple columns

[LP2803]

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	qq	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

[v-lionel-msft]

Hi @LP2803 ，

Thank you for your solution!

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LP2803 · Accepted solution]

https://community.powerbi.com/t5/Desktop/Count-of-rows-based-on-multiple-column/m-p/1562926/thread-i...

the solution for this question is posted at the above link. Thanks for everyone helped.

[v-lionel-msft]

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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LP2803]

@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

[amitchandak]

@LP2803 , Check if this can work

if(sumx(Filter(table, [MeetingInstanceID] = earlier([MeetingInstanceID]) && [ParticipantsName] = earlier([ParticipantsName])),if([Value]>5,1,0))>3,1,0)

[LP2803]

@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...

[amitchandak]

@LP2803 , Create a new column like

if(sumx(Filter(table, [MeetingInstanceID] = earlier([MeetingInstanceID])),if([Value]>5,1,0))>3,1,0)

[LP2803]

@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.