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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PowerBeeEye
Microsoft Employee
Microsoft Employee

Count unique rows in related table including zero

Hello,

 

I have two tables that look like this:

 

GroupNames
Group A
Group B
Group C

 

CompanyNameGroupNameMemberName
CompanyAGroupAMemberA
CompanyAGroupAMemberA
CompanyAGroupAMemberB
CompanyAGroupBMemberC
CompanyBGroupAMemberD

 

I would like to create a visualizer with a table that looks like this:

GroupNameMemberCount
GroupA2
GroupB1
GroupC0

 

Note that MemberCount counts only unique MemberName instances  for each GroupName.

There would be a slicer for CompanyName.

 

Thanks in advance!

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @PowerBeeEye ,

 

Try this:

 

1. Be careful of unnecessary spaces. Remove the sapces in the table below.

GroupNames
Group A
Group B
Group C

 

2. Create relationships.

relation.JPG

 

3. Create a measure.

Count = DISTINCTCOUNT(Company[MemberName])+0

count.gif

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

Hi @PowerBeeEye ,

 

Try this:

 

1. Be careful of unnecessary spaces. Remove the sapces in the table below.

GroupNames
Group A
Group B
Group C

 

2. Create relationships.

relation.JPG

 

3. Create a measure.

Count = DISTINCTCOUNT(Company[MemberName])+0

count.gif

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

Nathaniel_C
Super User
Super User

Hi @PowerBeeEye ,

Please check your expected results, you have four unique choices, and only 3 count.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




The expected results table only shows data from CompanyA, but otherwise, I believe it looks good

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors