Forum Discussion
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:
- Anonymous7 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
- AnonymousNot 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.
- v-xuding-msft
Community Support
Hi Anonymous ,
Can you please post a dummy file or the sample data and the expected output? Then we can help you as soon as possible.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Xue
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.