powerbi

[AtchayaP]

These are my tables,  Table 1--- skills- > employees have multiple skills, Development item---> Focussed to do in future.

In Table 2 and Table 3, you can see there is a relation from Table 1 SKILLS & Development item.

Table 1 basically denotes total organization information, from that Skills and Development items created in Table 2 and Table 3.

So, Table 1  has a total of 6 employees in an organization.  

My expected result is, that I want to bring the count of employees as 6 (table 1)  to table 3. anyone pls  Let me know the solution how it can be achieved.

@MahyarTF 

@amitchandak @Vijay_A_Verma 

Table 1 

Employee name	skills	Development item
arjun	Python, data, excel	Prepare my team
rithik	Program, java	0
edvid	Chemistry,physics,maths,biology	0
tharun	javascript	Training
elsa	0	mentorship
jose	0	Workflow, create model

Table 2

Employee name	skills
arjun	Python
arjun	data
arjun	excel
rithik	Program
rithik	java
edvid	Chemistry
edvid	,physics,
edvid	,maths,
edvid	biology
tharun	javascript

Table 3

Employee name	Development items
arjun	Prepare my team
tharun	Training
elsa	mentorship
jose	Workflow
jose	create model

Expected Output.

Employee name	Development items(count)	Count of employees from table 1
arjun	Prepare my team
tharun	Training
elsa	mentorship
jose	Workflow
jose	create model
Totals	5	6

[amitchandak]

@AtchayaP , Create two common table

Employee = distinct(union(distinct(Table1[Employee name]),distinct(Table2[Employee name]),distinct(Table3[Employee name])))

Development items= distinct(union(distinct(Table1[Development items]),distinct(Table2[Development items])))

Join the First one with all three tables and the second with Tables 1and 2, Now use measures from tables 1 and 3 with common dimension

refer: https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

[AtchayaP]

Employee name	skills
arjun	Python, data, excel
rithik	Program, java
edvid	Chemistry,physics,maths,biology
tharun	javascript
elsa	0
jose	0

Table 2

Employee name	skills
arjun	Python
arjun	data
arjun	excel
rithik	Program
rithik	java
edvid	Chemistry
edvid	,physics,
edvid	,maths,
edvid	biology
tharun	javascript

Table 3

Employee name	Development items
arjun	Prepare my team
tharun	Training
elsa	mentorship
jose	Workflow
jose	create model

This is how my tabel looks, I do not have Development items in table 1. just for explanation, I attached how development item relates to table 3.

Now can you tell me the possible solution?

[v-rzhou-msft · Accepted solution]

Hi @AtchayaP ,

I think you can try this code to create a measure to calcualte [Count of Employees from table 1].

Count of employees from table 1 = IF(HASONEFILTER('Table 3'[Employee name]),BLANK(),DISTINCTCOUNT('Table 1'[Employee]))

Result is as below.

Best Regards,
Rico Zhou

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