cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shanemc1 Frequent Visitor
Frequent Visitor

Convert SQL to Dax (Count Distinct using Join)

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

1 ACCEPTED SOLUTION

Accepted Solutions
shanemc1 Frequent Visitor
Frequent Visitor

Re: Convert SQL to Dax (Count Distinct using Join)

HI @Amit_PowerBIPro ,

 

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

5 REPLIES 5
Super User
Super User

Re: Convert SQL to Dax (Count Distinct using Join)

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

try this code:

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

Re: Convert SQL to Dax (Count Distinct using Join)

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 Frequent Visitor
Frequent Visitor

Re: Convert SQL to Dax (Count Distinct using Join)

HI @Amit_PowerBIPro ,

 

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

shanemc1 Frequent Visitor
Frequent Visitor

Re: Convert SQL to Dax (Count Distinct using Join)

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 

Super User
Super User

Re: Convert SQL to Dax (Count Distinct using Join)

ah, if the tables were not joined then my code will not work for sure


@Amit_PowerBIPro has provided you with working solution, so that's the most important Smiley Happy

 

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/