Join and group by two different tables

[Vin_1234]

Hi, 

How to Execute this sql query in Power Bi.

select
c.hrms_team_level_2,
c.hrms_team_level_3,
count (i.login_name) as num_users
from
[dbo].[psdp_capacity_enrich_pdr] c
join dbo.psdp_infra_logon_connections i
on i.nt_user_name = c.nt_login
group by
c.hrms_team_level_2, c.hrms_team_level_3

[Anonymous · Accepted solution]

Hi  @Vin_1234 ,

Create calculated table.

SUMMARIZE(
    FILTER(CROSSJOIN('psdp_capacity_enrich_pdr','psdp_infra_logon_connections'),
    [nt_login]=[nt_user_name]),[hrms_team_level_2],[hrms_team_level_3],[i.login_name],"num_users",
    COUNT(i.login_name)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.