Measure based on 2 tables

[Anonymous]

Hey,

Suppose I have a table called Hires as:

ID  NAME

1     A

2     B

3     C

.

.

And another Table Called Employees

ID   Country

 1      X

2       Y

3       Z

So I want to be able to calculate the hiring rate based on the two tables which would be count of ID in Hires table divided by count of ID's in the employee table. Is there a way that I can create a measure to calculate the hiring rate based on the ID's from the 2 tables? 

[PowerBSer · Accepted solution]

I think this can be done fairly easily.

Just use "new measure" and use DISTINCTCOUNT('Hires'[ID])/(DISTINCTCOUNT('Hires'[ID])+DISTINCTCOUTN('Employees'[ID])).  You may then want to format the measure as "%".

Please try it out and let us know if this works as I have not tested it myself.

[Anonymous]

IS there any way we can calculate a measure based on 2 different tables? These 2 tables are not connected with each other directly

[Anonymous]

@Anonymous

Yep, measures are independent of tables so you can reference any number of tables you like, even if they don't have a relationship

[Anonymous]

Ya, you can use the "Related()" DAX function in some ways to do calculations. 

[Anonymous]

Hey, can you please  explain with an example.

Suppose I have a table called Hires as:

ID  NAME

1     A

2     B

3     C

.

.

And another Table Called Employees

ID   Country

 1      X

2       Y

3       Z

So I want to be able to calculate the hiring rate based on the two tables which would be count of ID in Hires table divided by count of ID's in the employee table.

[Anonymous · Accepted solution]

@Anonymous There are many ways, easiest and cleanest way is using variables and here is a screenshot of the same.

Hiring Rate = 
VAR EmpCount = CALCULATE(DISTINCTCOUNT(Employees[Country]))
VAR HiresCount = CALCULATE(DISTINCTCOUNT(Hires[Country]))
RETURN DIVIDE(HiresCount,EmpCount,0)

[PowerBSer · Accepted solution]

I think this can be done fairly easily.

Just use "new measure" and use DISTINCTCOUNT('Hires'[ID])/(DISTINCTCOUNT('Hires'[ID])+DISTINCTCOUTN('Employees'[ID])).  You may then want to format the measure as "%".

Please try it out and let us know if this works as I have not tested it myself.

[Anonymous]

@PowerBSerThis one works. But the denominator is only count of employees

[Anonymous]

@Anonymous its diff to follow what you are referring to, can you please tag the appropriate people in your post? Also the denominator can be anything you want. I sent you a simple way to write the formula you can change it to what you want.

[Anonymous]

ya ok. thank you! @Anonymous

[Anonymous]

Hey,

Can you explain why we are adding ID of hires also in the denominator?

[PowerBSer]

My mistake.  If Employees is all-inclusive then you should not add it in the denominator.

[Anonymous]

@PowerBSer Ok, i will check it out and let you know.

Thank You!

[Anonymous]

@Anonymous

Wasn't that your question

"o I want to be able to calculate the hiring rate based on the two tables which would be count of ID in Hires table divided by count of ID's in the employee table. Is there a way that I can create a measure to calculate the hiring rate based on the ID's from the 2 tables? "

[Anonymous]

@Anonymous Hey I did not check your output while typing that. I was replying for the other solution.

I'll check this out and let you know!

[Anonymous]

@Anonymous if this is accepted solution, could you mark it as one.

[Anonymous]

Hey I am getting all 1 with this. The other solution gives me the correct answer. Thank You!