cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asjones Regular Visitor
Regular Visitor

Using Slicer with Inactive Relationships

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...

 

 

 

 

1 REPLY 1
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Using Slicer with Inactive Relationships

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.