cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
shanemc1 Frequent Visitor
Frequent Visitor

Distinct count of rows with multiple table joins in Power BI

Hi There,

 

I want to get a distinct count of Enquiries with Food and Drink in Power Bi. The way the database is structured is:

1 Enquiry could have multiple days, which have food and drink attached to them. I want to try and calculate how many enquiries have food and drink attached so I need to do a distinct count on the enquiry id in the Enquiries table where a record exists in the food and drink table

The table structure is as follows in SQL:

 

Enquiry Table

EnquiryId
10

Enquiry Day Table

EnquiryDayID     EnquiryId
5                10
6                10

Enquiry Day Food Drink Table

EnquiryDayFoodDrinkId      EnquiryDayId
20                         5
21                         6

Basically I want the distinct count to only return 1 as there is only one distinct enquiry with food + Drink attached.

Here is the code in SQL which return the correct number. I want to get the same result in Power BI.

 

select count (distinct(e.pkEnquiries))
from EnquiryDayFoodDrink edfd
inner join EnquiryDay ed
on ed.EnquiryDayId = edfd.EnquiryDayId
inner join Enquiries e
on ed.EnquiryId = e.pkEnquiries

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Distinct count of rows with multiple table joins in Power BI

Hi @shanemc1

 

You may check the relationship for your tables.Attached the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
LivioLanzo Super Contributor
Super Contributor

Re: Distinct count of rows with multiple table joins in Power BI

Hi @shanemc1

 

you can set up the belo model, I did quickly in Excel but in Power BI it's the same:

 

Capture.PNGCapture.PNG

 

Then use this measure:

 

=
CALCULATE(
    DISTINCTCOUNT( EnquiriesEnquriesDay[EnquiryId] ),
    SUMMARIZE( EnquiryDayFood, EnquiryDays[EnquiryDayId] )
)

 

 


 


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


Proud to be a Datanaut!  

Super User
Super User

Re: Distinct count of rows with multiple table joins in Power BI

So, you should be able to do Merge queries in M for this, is that what you are looking for? Otherwise, perhaps NATURALINNERJOIN in DAX?


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

shanemc1 Frequent Visitor
Frequent Visitor

Re: Distinct count of rows with multiple table joins in Power BI

Hi @LivioLanzo ,

 

I am not clear on how I can set up these new tables? I already have Enquiries, EnquiryDay and EnquiryDayFoodDrink imported from SQL database into power BI. How do I populate these new tables?

 

Thanks,

Shane

shanemc1 Frequent Visitor
Frequent Visitor

Re: Distinct count of rows with multiple table joins in Power BI

Hi @Greg_Deckler,

 

I am not clear on what you mean or how to do this? Could you please show an example of how this would work?

 

Thanks,

Shane

LivioLanzo Super Contributor
Super Contributor

Re: Distinct count of rows with multiple table joins in Power BI

@shanemc1

 

you need to use the 3 tables you already have plus the 3 tables on top in my picture which, if you notice, are just the distinct key of their related table. And then set up a one-to-many relationship going from the the PK to the FK as per my picture

 


 


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


Proud to be a Datanaut!  

shanemc1 Frequent Visitor
Frequent Visitor

Re: Distinct count of rows with multiple table joins in Power BI

@LivioLanzo I understand the concept of doing this but I am not sure how to create the 2 new tables which store these values?

LivioLanzo Super Contributor
Super Contributor

Re: Distinct count of rows with multiple table joins in Power BI

@shanemc1

 

they're just the distinct values of the tables you already have, therefore you just need to SELECT DISTINCT if you're using SQL or Remove Dusplicates if using Power Query, there's nothing special to it

 


 


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


Proud to be a Datanaut!  

v-cherch-msft Super Contributor
Super Contributor

Re: Distinct count of rows with multiple table joins in Power BI

Hi @shanemc1

 

You may check the relationship for your tables.Attached the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

shanemc1 Frequent Visitor
Frequent Visitor

Re: Distinct count of rows with multiple table joins in Power BI

Hi @v-cherch-msft,

 

This solution worked fine.

 

I already had the existing relationships with the tables but I was unsure of the Dax Syntax to count the distinct rows.

 

Thanks very much for your help.

 

Shane

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 941 guests
Please welcome our newest community members: