Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @shanemc1
You may check the relationship for your tables.Attached the sample file for your reference.
Regards,
Cherie
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?
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
Hi @shanemc1
you can set up the belo model, I did quickly in Excel but in Power BI it's the same:
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
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |