Direct Query DataVerse left join between two entites

[HichamGRC]

Hi All,

I'm new to Power BI and I struggle to make the report that I need. 

I'm using Direct query to get data from DataVerse (dynamics 365). I have two entities contact and User and what I want to is to display for each User the number of the contacts created. 

Using SQL, this is the query : 

select
    systemuser.fullname,
    COUNT(contact.createdby) as countContacts
from systemuser  
left join contact ON contact.createdby = systemuser.systemuserid
group by systemuser.fullname
order by countContacts desc

I tried to achieve using a measure but it didn't work as Power bI doesn't know how to make the join based on the fields I want. 

I look at Left outer Join merge queries but I don't want to update the entity systemUser itself. 

Thanks for the help,

Hicham

[HichamGRC]

Hi @amitchandak 
Thanks for the help, I spent time working on data modeling and I achieved that by creating relationships.

[amitchandak · Accepted solution]

@HichamGRC , You need to create join between contact and  systemuser ( Many to one)

And then just create a measure

count(contact[contact.createdby])

and plot it in visual with systemuser.fullname and sort visual on This new measure