Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LP2803
Responsive Resident
Responsive Resident

Count of rows based on multiple column

Hi Team,

First of all, sorry to create this duplicate thread for another thread i have created few days ago.  i'm unable to post screen shot in that thread so creating a new thread.

 

Below is how my data looks like.  

 

I have MeetingInstanceID, ParticipantName, Attribute2, Value as columns.

 

This is the meeting information in my organisation and all attributes (packetloss, latency etc... ) are recorded at 1 min interval for every meeting.   In this data "Value" column is the values recorded at each min for each attribute, participantname, meetinginstanceid.

 

I need to create a flag for each meetinginstanceid when it meets the below criterias.

1. participant hitting latency of >400 for morethan 5 times in a meeting

2. participant hitting packetloss of >5 for morethan 3 times in a meeting

 

then flag that meeting as 1 or something so that my manager can know this meetinginstanceid needs attention.

 

LP2803_0-1608561110090.png

 

My output to my manager looks as below :

 

LP2803_0-1608561735085.png

 

 

 

 

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@LP2803 

Right. Change it a bit:

Measure V2 =
IF(SUMX ( DISTINCT ( Table1[ParticipantName] ), [PacketLoss?] ) >0, 1, 0 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

7 REPLIES 7
AlB
Super User
Super User

@LP2803 

Right. Change it a bit:

Measure V2 =
IF(SUMX ( DISTINCT ( Table1[ParticipantName] ), [PacketLoss?] ) >0, 1, 0 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

LP2803
Responsive Resident
Responsive Resident

Perfect. it worked. Thanks a lot. 

 

I will definitely contact you privately for some DAX tutorial sometime.

AlB
Super User
Super User

@LP2803 

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Try this new measure, based on the one you have:

Measure =
SUMX ( DISTINCT ( Table1[ParticipantName] ), [PacketLoss?] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

LP2803
Responsive Resident
Responsive Resident

Im working on the sample workbook for you.  Meanwhile, I need a flag with 1 or 0 to tell the user that this meeting has packetloss issues. The Sumx, sums the values right?

AlB
Super User
Super User

Hi @LP2803 

The second pic is not readable. Too small.

Can you share the pbix?

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

LP2803
Responsive Resident
Responsive Resident

 

@AlB Thanks for the response, Sorry im unable to attach the pbix here.  Below is the better image i think. 

 

Notice the underlined meetinginstanceid, it shows 1 for packetloss indicating that this meeting has packetloss issues morethan 3 times which is not correct becuase its calculating for the overall meeting and not per participantname.   

LP2803_0-1608568922238.png

 

when i expand to the participant level (image below),  the packetloss column becomes 0, which is actually correct since no participants are having morethan 3 times of packet loss in their meeting.   

 

LP2803_1-1608569131086.png

 

 

 

LP2803
Responsive Resident
Responsive Resident

currently the latency? packet?  measures (with below DAX)  i have in the view are working fine but not considering the participantname in them. 

 

PacketLoss? = if(COUNTROWS( FILTER(
Filter('Sheet1 (2)','Sheet1 (2)'[Attribute.2]="packetLoss"),
'Sheet1 (2)'[Value]>5)) > 3,1,0)

 

For example, in the second image, I have a matrix visual where I have given the drilldown from meetinginstandid to participantname.

Here, for some meetinginstanceids, it gives "1" as it counts the packetloss for all participants based on the criteria given.  and when I drilldown to participantname, there wont be any participants with packetLoss >5 for morethan 3 times in the meeting. 

 

So i need to calculate per participant.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.