cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
Community Support Team
Community Support Team

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.
9 REPLIES 9
Super User
Super User

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?


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

Super User
Super User

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?

Super User
Super User

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!  

Highlighted
Community Support Team
Community Support Team

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