one id code used in different tables

[RaulPBI]

hi everybody

I have three tables, one of them is dimensional (id and names of my employees) and it has unique values. The others are fact tables, with diffenrent values (hours in differents sections or activities). I want to calculate total number of employees and I had some duplicated value because some people work in more than one activity. So, the measure shows more employees that I really have. Any idea about how to calculate this properly?

Thank you in advance.

[drewlewis15]

What measure are you currently using?  Would CALCULATE(DISTINCTCOUNT... solve your issue?

[RaulPBI]

I'm afraid no. More details?

[drewlewis15]

What measure are you currently using?

[RaulPBI]

I use "distinctcount" to count different values (worker´s number) in both fact tables, but the issue is that one single value can appear in both tables. So, how do you do to consider this repeated value as one value?

[Anonymous]

HI @RaulPBI,

Can you share some sample data for test?

Regards,

XIaoxin Sheng

[RaulPBI]

What I mean is that I have the workers in tables "horasproduccionnaves" and "tablahoras1", but they are unique workers. I want to calculate "number of workers". Any help?

[Anonymous]

HI @RaulPBI,

Maybe you can try to use UNION function to merge these tables and use COUNTROWS function to get the count.

Sample:

Count workers =
COUNTROWS (
    DISTINCT ( UNION ( ALLSELECTED ( Table1[ID] ), ALLSELECTED ( Table2[ID] ) ) )
)

Regards,

Xiaoxin Sheng

[RaulPBI]

That is no work because the tables have not the same columns.

[Anonymous · Accepted solution]

Hi @RaulPBI,

I think you need to create a mapping table to store mapping records and used to convert different id.

Sample:

Index	Table1 ID	Table2 ID
1	123	abc
2	456	def

Regards,

Xiaoxin Sheng