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

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
Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
Solution Sage
Solution Sage

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!  

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

 

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!  

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

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.

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

@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!  

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.