Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Measure based on 2 tables

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? 

Need Help
  • PowerBSer's avatar
    PowerBSer
    8 years ago

    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's avatar
    Anonymous
    8 years ago

    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)

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

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

  • PowerBSer's avatar
    PowerBSer
    Frequent Visitor
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Hey,

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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's avatar
      Anonymous
      Not applicable
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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's avatar
    Anonymous
    Not applicable
    8 years ago

    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)