relationships and filtering two charts from two datasets

[siddrow]

Hi

I have 3 filters.

filter 1 = division (data source for this is "tasks")

filter 2 = team (data source for this is "tasks")

filter 3 = project (data source for this is "tasks")

I have 2 charts that these filters are connected to.

chart 1 = actions by person (data source for this is "actions" - using columns "owner" and "status" )

chart 2 = tasks by person (data source for this is "tasks" - using columns "name" and measure "count tasks")

relationships:

- data set "actions" using column "division" and data set "tasks" using column "division". This is a many to many and the cross-filter is single (tasks)

Having trouble trying to workout what other relationships I need to create to get the filter for "project" and "team" to work across both charts. Both datasets have the same column called "project" but because I've already made a relationship with that dataset with other columns, it's not working. I'd like all 3 filters to filter both chart data. Obviously only the "division" filter is working across both charts at the moment, and "team" and "project" filter only filters chart 2 correctly as the field in the filter is from the same data set as that chart. 

please help 🙂 

[amitchandak · Accepted solution]

@siddrow , Is actions and task are two fact tables. if yes, you should have common dimensions joining with both of these to filter

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

[Anonymous · Accepted solution]

Hi @siddrow ,

To get the "project" and "team" filters to work across both charts, you need to create additional relationships and possibly use a bridge table to handle the many-to-many relationships. You can create below bridge table and build relationship in your model.

BridgeTable = 
DISTINCT(
    UNION(
        SELECTCOLUMNS('tasks', "Project", 'tasks'[project], "Team", 'tasks'[team]),
        SELECTCOLUMNS('actions', "Project", 'actions'[project], "Team", 'actions'[team])
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@siddrow , Is actions and task are two fact tables. if yes, you should have common dimensions joining with both of these to filter

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

[siddrow]

Hi

Thanks so much for your relpy.

When I create the tables, there are duplicates so the relationship can only be many to many. Will this cause issues as I see your video shows one to many relationships.