Creating Calculated Column Based on Multiple Tables and Filtering

[amanda_wahlig]

I have multiple tables tracking the work of our staff (completely different types of work items means we can't use the same table to track it all) - each work item is assigned to a staffer, so obviously there are many rows with the same staffer's name . Not every staffer is in every table.

I have a report where I have visuals from the different tables, and I would like to have a slicer where I can filter all the visuals based on a staffer's name.

I'm pretty sure that the best way to do this is to combine the staffer name columns from each table into a new table, then filter out the repeats (or is that step even necessary)?

I welcome any tips, suggestions, or code.

[Idrissshatila · Accepted solution]

hello @amanda_wahlig ,

yes, have a dimension table that have the distinct staff names and link it to all the tables with the records.

then you can see the data from all tables with this staff name from the dimension table.

[amanda_wahlig]

I was more looking for tips on creating that dimension table with all the names, but I appreciate it. I wound up extracting the columns with attorney names into new tables and merging queries, then removing duplicates.

[amanda_wahlig]

I was mostly looking for tips on how to create the dimension table. I wound up referencing the needed columns and appending them into a new table, then removing duplicates. That way we don't have to manually change the reference table.

[audreygerred · Accepted solution]

Hello! Ideally you always want to model to a star schema (fact and dimensions). The employee table would be a dimension table in this case and the joins to the fact tables allow the fact tables to be used together in a visual through fields from the dimension. Here is a blog I wrote about star schema: http://powerbiwithme.com/2023/07/30/the-star-schema-edition/ 

[Idrissshatila · Accepted solution]

hello @amanda_wahlig ,

yes, have a dimension table that have the distinct staff names and link it to all the tables with the records.

then you can see the data from all tables with this staff name from the dimension table.