Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

Need Help
  • Stachu's avatar
    Stachu
    6 years ago

    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 

7 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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