Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Hi @amitchandak
Thanks for the help, I spent time working on data modeling and I achieved that by creating relationships.
@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
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |