Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TBensen
Helper I
Helper I

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

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 YearsEmployee IDRecord No.Location
2emp_1123Location_1
2emp_1234Location_1
4emp_2345Location_2
7emp_3456Location_3
5emp_4567Location_1

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

Seniority Counts of Records.png

 

Employee Table:

Employee IDYears of ServiceLocation
emp_13Location_1
emp_27Location_2
emp_310Location_3
emp_47Location_1

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

Seniority Counts of Population.png

 

With the help of @DataZoe , I created the relational tables like this:
Seniority Tables.png

 

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 YearsGrouped Seniority YearsLocation
00 to 1 YearLocation_1
00 to 1 YearLocation_2
00 to 1 YearLocation_3
00 to 1 YearLocation_4
10 to 1 YearLocation_1
10 to 1 YearLocation_2
10 to 1 YearLocation_3
10 to 1 YearLocation_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

1 ACCEPTED SOLUTION
DataZoe
Microsoft Employee
Microsoft Employee

@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?
 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

6 REPLIES 6
DataZoe
Microsoft Employee
Microsoft Employee

@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?
 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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
Microsoft Employee
Microsoft Employee

@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... 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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

 

Thanks,
Trevor Bensen

parry2k
Super User
Super User

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hello,

 

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


Thanks,
Trevor Bensen

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.