In answer to your questions, Disconn Emp is created as a DAX Expression Table using VALUES(). It could just as easily be created in Power Query (and probably should be). I recently did a video explaining the three types of Power BI tables that I think you’ll find very helpful in understanding this solution.
For the second issue, I’m going to show you a trick that blew my mind when I saw it. You can actually distinguish between different items/people with the same name using invisible characters tacked onto the end of the column value. The SQLBi guys were the ones I first saw do this using DAX, and I’ve adapted this approach into Power Query using this basic pattern:
-
Group in PQ on Full Name and add an All Rows aggregation called [Data]
-
Add an index within each grouping with Custom Column:
=Table.AddIndexColumn([Data], “Index”, 1, 1)
- Add calculated column concatenating relevant field with (Index - 1) UNICHAR (8204) nonprinting character
Article link: https://www.sqlbi.com/articles/handling-customers-with-the-same-name-in-power-bi/
The final question to solve is how to distinguish between say, the three distinct employees named “Mike Jones” in the slicer. After you apply the invisible characters trick above, you will have three Mike Joneses in the slicer, but no idea which one is which. If you’re willing to use custom visuals, OK Viz has an awesome slicer enhancement called Smart Filter Pro that I use all the time. One of the enhancements that it has is that it can display a measure result next to the slicer choice. In this case you could use a LOOKUPVALUE measure to identify a distinguishing characteristic for each employee (e.g., job title, division, “Cooks Fish in the Common Microwave” Mike Jones vs “Never Refills the Printer” Mike Jones, etc.).
I hope that addresses all of your questions/concerns. If you run into any other problems, just give a shout.
– Brian