Convert SQL to Dax (Count Distinct using Join)

[shanemc1]

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

[Anonymous]

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

[shanemc1 · Accepted 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

[Stachu]

There is an active direct join between these tables, correct?

try this code:

CALCULATE(DISTINCTCOUNT('Enquiries_Venues'[fkEnquiries]),  VenuePremium[VenueId]) <> BLANK())

[shanemc1]

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 

[Stachu]

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/