Count Distinct combining two tables

[MarcoDekker]

Hi all,

Hope you can help me. Been struggling with this for some time now. I have two tables: one with employee data and another with their projectassignments. I just need a simple grid with their names and number of assignments. Can you please help as this count of assignment measure drives me mad.

Thanks in advance.

[MarcoDekker]

Great! thanks @Greg_Deckler!

[MarcoDekker]

hi @Greg_Deckler, Thanks a lot. it works indeed! I have one more question.

In table 1 i have more columns. When i create the report and add more attributes from this table, i get some kind of cartesian product. Why is this?

b.t.w. i create visualisations with direct query on a tabular model.

[Greg_Deckler]

@MarcoDekker I'm not sure. Can you share a screen shot or more information?

[MarcoDekker]

Hi @Greg_Deckler .  Alas, not able to reproduce this behaviour in the test pibx.  Will continue to try.

But i realised, i miss Jane in the output. Can we generate some kind of left outer join in Dax? Jane does not have projects assigned, yet she need to show up in the report. 

[Greg_Deckler · Accepted solution]

@MarcoDekker Try: 

Measure = COUNTROWS(DISTINCT(SELECTCOLUMNS('Table 2',"__name",[assignedprojectname]))) + 0

[Greg_Deckler]

@MarcoDekker Try:

Measure = COUNTROWS(DISTINCT(SELECTCOLUMNS('Table 2',"__name",[assignedprojectname])))