Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AtchayaP
Helper V
Helper V

powerbi

 

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

1 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.

RicoZhou_0-1659607218451.png

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.

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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?

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.

RicoZhou_0-1659607218451.png

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.