Reply
Frequent Visitor
Posts: 11
Registered: ‎02-11-2019
Accepted Solution

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

 


Accepted Solutions
Community Support Team
Posts: 1,402
Registered: ‎07-25-2018

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

Attachment

All Replies
Super User
Posts: 765
Registered: ‎09-16-2018

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? Mark my post as a solution!


Proud to be a Datanaut!

Super User
Posts: 10,605
Registered: ‎07-11-2015

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!


Frequent Visitor
Posts: 11
Registered: ‎02-11-2019

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

Frequent Visitor
Posts: 11
Registered: ‎02-11-2019

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
Posts: 765
Registered: ‎09-16-2018

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? Mark my post as a solution!


Proud to be a Datanaut!

Frequent Visitor
Posts: 11
Registered: ‎02-11-2019

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
Posts: 765
Registered: ‎09-16-2018

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? Mark my post as a solution!


Proud to be a Datanaut!

Community Support Team
Posts: 1,402
Registered: ‎07-25-2018

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.
Attachment
Frequent Visitor
Posts: 11
Registered: ‎02-11-2019

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