Using Slicer with Inactive Relationships

[asjones]

I have tables “MainProject” and “WorkOrder” and both have a field named DEPARTMENT.

I also have a 3rd table called “Department_Relations”.   Department relations has a list of many departments, countries, and areas. (More departments than countries, and more countries than areas).

I have an active relationship from MainProject[Department] many to 1 DepartmentRel[Department].

Also have an inactive relationship for WorkOrder[Department] many to 1 DepartmentRel[Department]

1. Ideality in this case I would like one slicer to that would filter on the “xArea” selected in the slicer. It would filter on both  relationships at the same time. However in searching I could not figure out how to even get started on that.

1. My second option was to have one slicer for MainProject (active) and a 2nd slicer on WorkOrder (inactive) and use the USERELATIONSIHP() function with either a measure or calculated column. However I seemed to get stuck on the DAX around that. I tried creating Measure (one article seemed to suggest that) another seemed to suggest a Calculated Column but then I read Calculated Columns could be tricky to do with USERELATIONSHIP().

I tried the following options and the putting the measure or calculated column in a slicer, but they did not “slice”.

Measure1 = CALCULATETABLE( DISTINCT('Department Relations'[xArea] ) , USERELATIONSHIP(Work_Order_Master[WMF_Department], 'Department Relations'[Department]) )

This calculated column was in the Department Relations table.

Calc1 = CALCULATETABLE( DISTINCT('Department Relations'[xArea] ) , USERELATIONSHIP(Work_Order_Master[WMF_Department], 'Department Relations'[Department]) )

I would love to hear your thoughts and ideas for one or both options from above.

Thanks

Alan

I also tried some ideas from this post but did not get anywhere.

https://community.powerbi.com/t5/Desktop/Using-inactive-relationship-for-filtering-purposes/m-p/5845...

[Anonymous]

@asjones,

Slicers don't utilize inactive relationships, thus when you create slicer using fields of DepartmentRel table, and select values in the slicer,  visuals that contains WorkOrder fields will not be filtered.

In your scenario, I note that you create a new table using the following DAX. Could you please share sample data of your  tables and post expected result? Also it is not possible to drag measure to a slicer.

CALCULATETABLE( DISTINCT('Department Relations'[xArea] ) , USERELATIONSHIP(Work_Order_Master[WMF_Department], 'Department Relations'[Department]) )

 

Regards,
Lydia