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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter by related table

Hi guys,

 

I am building a report in Power BI and I want to show the hours of the teams in my company.

For one visual I need the table Projects and TimeTransactions, for another I need just the TimeTransactions.

I want to add a slicer to filter on the different teams, but this doesnt seem to do anything.

Can someone help me with this?

 

I added a picture of the tables and their relation to clarify my structure.

Model.PNG

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

try this measure:

Quantity_New =
CALCULATE (
    SUM ( 'TimeTransactions'[Quantity] ),
    TREATAS ( VALUES ( EmployementOrganization[Employee] ), Employees[Employee] )
)

it shows the same values for Department 1 and 3, but I think that's because Employee A is in both departments 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

7 REPLIES 7
Stachu
Community Champion
Community Champion

in which table are the teams?

The filter will go with the directions that is shown by the arrows, so e.g. slicer based on 'Employees' WILL propagate to 'TimeTransactions' and 'EmploymentOrganization' but WILL NOT propagate to 'Projects' and 'Departments'

 

Also it's a bad idea to change the relationship to bidirectional because of ambiguity (more details here https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/, around 14:30), it's better to add relevant logic in the measure



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

The teams can be found in Departments and the combination of teams and their employees can be found in Employment Organizations

Stachu
Community Champion
Community Champion

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window). Also please specify which column from 'TimeTransactions' you want to show

Column1Column2
A1
B2.5

the solution will most likely be to use TREATAS to create a virtual relationship (https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/), but without specific example I cannot help more



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

I made a power Bi file with the data.

I hope this link works, if it doesn't please let me know.

//Link removed

Stachu
Community Champion
Community Champion

try this measure:

Quantity_New =
CALCULATE (
    SUM ( 'TimeTransactions'[Quantity] ),
    TREATAS ( VALUES ( EmployementOrganization[Employee] ), Employees[Employee] )
)

it shows the same values for Department 1 and 3, but I think that's because Employee A is in both departments 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

Works like a charm, Thanks!

Stachu
Community Champion
Community Champion

glad to help, have a good day 🙂



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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