Adding a Common column from two tables into a created table that is shared between the two table

[TBensen]

Hello (@DataZoe),

Hopefully the subject makes sense and this issue is stemming from a previous post I had about creating a counted summary table found here: https://community.powerbi.com/t5/Desktop/Create-a-Counted-Summary-Table/m-p/1637315#M657872

The answer to this post had me create a table that used a column called Seniority years that was found in my two tables, Employees and Incidents.  I created a relationship between the 3 tables by using the created Seniority Table and this allowed me to generate a Rate of Incidents based off of the seniority year number.

Here are the tables that exist.

Incidents Table:

Seniority Years	Employee ID	Record No.	Location
2	emp_1	123	Location_1
2	emp_1	234	Location_1
4	emp_2	345	Location_2
7	emp_3	456	Location_3
5	emp_4	567	Location_1

With this table, I can get a count of incidents by Seniority:

Employee Table:

Employee ID	Years of Service	Location
emp_1	3	Location_1
emp_2	7	Location_2
emp_3	10	Location_3
emp_4	7	Location_1

With this table I get a count of the organization population based off of the Seniority years.

With the help of @DataZoe , I created the relational tables like this:

Using these tables I created a grouping on the Seniority Years column and I want to be able to use a slicer across all visualizations to show the breakdown of the Rate of Incident per location.

If a user selects Location_1, we should see the count of incidents for all of Location_1 and the population of people at Location_1, which should give us a Rate of Incident by Seniority group per Location Population.  Currently if I add a location slicer, and 1 location is selected, I get a count of incidents per location / total population per seniority groupings.

I think I need to add these locations to the Seniority table like what is shown below, but I'm not sure what the best way to go about this is.

Seniority Years	Grouped Seniority Years	Location
0	0 to 1 Year	Location_1
0	0 to 1 Year	Location_2
0	0 to 1 Year	Location_3
0	0 to 1 Year	Location_4
1	0 to 1 Year	Location_1
1	0 to 1 Year	Location_2
1	0 to 1 Year	Location_3
1	0 to 1 Year	Location_4

I think once I have a table that looks like this I should be able to add a slicer to the report that allows me to select a location and see the data represented based on that Location selection.

Thanks,

Trevor Bensen

[DataZoe · Accepted solution]

@TBensen I think you want to see the data by location (both people and incidents), for which you can create a location table and set it up like the seniority years table (connected to both tables). You can do this with SUMMARIZE to combine and de-dup the locations from both tables. 

Locations = DISTINCT(UNION(SUMMARIZE(Employee,Employee[Location]),SUMMARIZE(Incident,Incident[Location])))

Then join like you did seniority years.

Does that answer your question?

[TBensen]

Hello @DataZoe,

This is exactley what I needed! Thank you very much!

Another quick question where would be the best place to learn more about Power BI?  Is there a YouTube channel that does a good job going through the different DAX formulas or a book I should read?  Just wondering, because I want to learn more about it.

Thanks again,

Trevor Bensen

[DataZoe]

@TBensen Awesome, I'm glad that worked 🙂

There are a lot of fantastic resources out there, which is sometimes a little overwhelming! I personally found the SQLBI.com guys to great for me when I was getting started (I did one of their trainings too) and I'm still learning stuff from them! @Tahreem24 also has a great post on her linkedin with great resources organized by category https://www.linkedin.com/posts/tahreem-ansari-44561a16b_powerbi-athomewithpowerbi-taik-activity-6700... 

[TBensen]

Awesome, thank you again for your help!  I will check out these resources.

Thanks,
Trevor Bensen

[parry2k]

@TBensen you should post sample raw data and expected output.

[TBensen]

Hello,

Thank you for the tip.  I have updated the posting with more information.

Thanks,
Trevor Bensen