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.
I have the following table structure in my database.
Enquiry Table which stores enquiries
EnquiryVenue Table which stores venues that are attached to an enquiry
Venue Table which stores venue table
VenuePremium table which stores venues that are on our premium system.
I want to count the number of Enquiries that have a venue attached to it that is in the premium table.
The following sql captures this data
select count(distinct fkEnquiries) from enquiries_venues ev inner join VenuePremium vp on ev.fkVenue = vp.VenuePremiumId
In Power BI I am doing the following:
CALCULATE(DISTINCTCOUNT('Enquiries_Venues'[fkEnquiries]),SUMMARIZE('VenuePremium', VenuePremium[VenueId]) )
This is giving me a much larger count than expected ?
Any suggestions on how this should work?
Thanks in advance
Solved! Go to Solution.
HI @Anonymous ,
Yes it seems to be running a left join because when I do a left join in SQL I am presented with the same number.
I have tried using a NaturalInnerJoin and it worked perfect thanks for your help.
Shane
Hi @shanemc1,
I think LEFT JOIN is taking place when you are using SUMMARIZE function in dax.
Can you re-run that SQL query with left join and see the count?
I think you can modify your dax query to use NATURALINNERJOIN(enquiries_venues,VenuePremium)
Thanks,
Amit
HI @Anonymous ,
Yes it seems to be running a left join because when I do a left join in SQL I am presented with the same number.
I have tried using a NaturalInnerJoin and it worked perfect thanks for your help.
Shane
There is an active direct join between these tables, correct?
try this code:
CALCULATE(DISTINCTCOUNT('Enquiries_Venues'[fkEnquiries]), VenuePremium[VenueId]) <> BLANK())
Hi @Stachu ,
I tried this and it just returned true. The tables are not directly related in power bi but the venue id in both do correspond to each other
ah, if the tables were not joined then my code will not work for sure
@Anonymous has provided you with working solution, so that's the most important 🙂
here is a good more in depth aritcle on using joins in DAX and the implications
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |