Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How do I create a measure that references two different tables

I am working on a project that requires I calculate par hours per employee. To do so, I need to know the number of working days that an employee has been active for and their par hours per day (which is pulled from a database). I have a calculated column that can find the number I need for a given period when specified in the Dax expression. What I would like is to convert this to a measure so that the numbers will be effected by a slicer in my report visuals.

 

I am pulling from two different tables: a date table and a employee time entry table. My calculated column takes a sum of a column called Work Day from my date table based on whether the date in the date table is after the start date of an employee found in the employee time entry table and less than or equal to today's date. I would like to convert this to a measure so that I can use a slicer in my visualization to effect the par hours and total hours shown per employee. The tricky thing about this is that the employee time entry table is not grouped by employee_id. So for each employee_id, it shows every time entry they have made into the system. If more information is needed I can try to explain better. Thanks in advance for help.

 

My calculated column formula is as follows:

Par = CALCULATE(SUM('Date'[Work Day]), FILTER('Date', 'Date'[Date] >= 'Time Entry 2'[Start Date] && 'Date'[Date] <= TODAY())) * 'Time Entry 2'[Hours in Day]
  • Anonymous's avatar
    Anonymous
    7 years ago

    Everything I've had to do thus far for things like this was to get the tables merged into a singular query.  Manipulate the date info, so that it's supportive of an employee record etc.  I haven't been able to have like what you wanted (and I did too) which was to have multiple different queries and have a slicer affect them all.  The way I solved this was to create modular queries that worked as autonomous tables, and then a set of queries that built joins upon those "atomic level" queries.  Then I could get the visuals to all filter nicely.

     

    hope it helps.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Everything I've had to do thus far for things like this was to get the tables merged into a singular query.  Manipulate the date info, so that it's supportive of an employee record etc.  I haven't been able to have like what you wanted (and I did too) which was to have multiple different queries and have a slicer affect them all.  The way I solved this was to create modular queries that worked as autonomous tables, and then a set of queries that built joins upon those "atomic level" queries.  Then I could get the visuals to all filter nicely.

     

    hope it helps.