How to use a slicer to constrain values from multiple tables lacking active relationships

[T2]

I need to show two table visualizations on a page.

One will show columns from the Issues table only.

The other will show columns from the Risks table only.

The Workstreams table is the parent.

The Issues and Risks tables are children.

There are active relationships between the Workstreams table and the Issues table, and between the Workstreams table and the Risks table.

I want to use a single slicer to only show Issues and Risks whose Category column is selected in a slicer on the Category table's Category column.

However, because the Category table creates a circular relationship when I add the second relationship between Category and Risks, that relationship is set as Inactive.

Because it is an inactive relationship, I cannot easily use the slicer.

It *feels to me* the DAX required to deal with this should not be difficult. However, I cannot find the answer. I see lots of examples of similar questions but none clearly answering the question, using more words, how can one use a single slicer to constrain rows from multiple tables when the slicer source table does not have an active relationship with all of those tables.

Help, please.

Thank you!

[d_gosbell · Accepted solution]

Do you really need the two relationships from Workstreams to be Bi-Directional? If you change them to a single direction filter you will then be able to activate the relationship between Category and Risks and you will get the behaviour you are after without any special DAX.

[v-eachen-msft · Accepted solution]

Hi @T2 ,

You could use a single slicer table ( no relationship with other tables ) and create measures to show the comparison results ( like TRUE/FALSE or 1/0 ).
You could refer to the following DAX:

Measure = 
IF(SELECTEDVALUE(Slicer[ID])=SELECTEDVALUE(risk[ID]),1,0)

Then you could set measure in the filter pane to show the result you want.

Here is my test file for your reference.

[T2]

Thanks! This DAX does work as indicated.

One caveat: I did not state it in my original request but I am expecting users to select one or more of the options in the slicer. This solution doesn't work for multiple selections.

[d_gosbell · Accepted solution]

Do you really need the two relationships from Workstreams to be Bi-Directional? If you change them to a single direction filter you will then be able to activate the relationship between Category and Risks and you will get the behaviour you are after without any special DAX.

[T2]

It worked as you said it would. I did not expect directionality to be the issue.

I'm going to have to read up to understand the "why" of this because I still have a circular join, which I thought would not be allowed.

Best of all, your solution works with one or more slicer options selected, which, while not specified, is a requirement for me.

Thank you very much!