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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Spencer
Helper II
Helper II

Relationships and Slicer

Hi

 

I'm trying to sort out the relationships shown in my screenshot below in order to get my DeptCode slicer working with my sumabsenthrs measure on the visual level.

 

My sumabsenthrs measure sums the [ern_worked_hrs] in the Payroll Table with a filter that selects only those that have a certain [pay_code]:

sumabsenthrs = CALCULATE(SUM(Payroll[ern_worked_hrs]),RELATEDTABLE(PayCodesTable),PayCodesTable[bi_pay_code]="absent")

 

The Payroll Transactions Table contains the department in the [ypt_department] column which is currently linked to the DeptTable with [DeptCode]. And the Payroll and Payroll Transactions Table are linked by the [row_id] column in each.

 

I have been able to get the sumabsenthrs measure to work with slicers from the DateTable but not the DeptTable.

 

Any help is much appreciated.

Thanks

RelationshipsRelationships

 

 

6 REPLIES 6
Greg_Deckler
Super User
Super User

So, if you create a DeptTable slicer and then add a table with "Payroll Transactions" and put the row_id column in it, I expect that the slicer filters that table, correct?

 

Now, if you add a table with "Payroll" table and put the row_id column in it, does that filter correct as well?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Yes @Greg_Deckler those both filter correctly.

OK, so the next question is if you add the PayCodes table, does it filter correctly? I am assuming it doesn't but basically am trying to remove this from the realm of a DAX calculation and prove that it is entirely related to table relationships.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Sorry for taking your time but I have managed to solve the issue. I changed the relationship between my Payroll and Payroll Transactions to be row id to pfh_pay_date instead of rowid to rowid. It was actually an issue with the DateTable and not the DeptTable my apoligies.

The measures are slicing by both the Date and Dept now!

I did spot the problem when I went through your second question so thankyou very much for your assistance.

No problem, glad asking the dumb questions led to the solution!



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

 

Sorry to bring you back to this topic but I've just realised my problem isn't fully resolved.

Only a small selection of departments are being sliced, and the rest are being lumped into a (blank) category when visualsied.

 

I've gone back to your first reply and when slicing by the DeptCode, the Payroll table with row_id dosen't slice correctly on all departments.

 

Thanks

 

Here's an updated screenshot: (Employee Master contains each employees current department in em_department)
Relationships w EM tableRelationships w EM table

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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