Filter by related table

[Anonymous]

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.

[Stachu]

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

[Anonymous]

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

[Stachu]

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

Column1	Column2
A	1
B	2.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

[Anonymous]

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 · Accepted solution]

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 

[Anonymous]

Works like a charm, Thanks!

[Stachu]

glad to help, have a good day 🙂