USERELATIONSHIP not working as expected. Could use some suggestions.

[Joe_Marangos]

Hi all,

Im working on a report that invovles interactive mapping. The complexity with the project is that there are different legends for different data layers which means I have to use a table to get a working legend. 

I have two key tables: Legend_Table and Pollution_Data. 

I have a slicer that switches the map data (which is using Pollution_Data) between NO2 and NOX.

Within these tables I need the following relationships established for filtering:

Legend_Table[Cat] <-> Pollution_Data[NO2_Cat]

Legend_Table[Cat] <-> Pollution_Data[NOX_Cat]

Legend_Table[Cat] contains all the values that would match with either the NO2_Cat or NOX_Cat collumns. 

Obviously, connecting these relationships and trying to make them both active does not work. So I have been attempting to use "USERELATIONSHIP" to switch between the relationships depending on the data selected. 

As a test run I did one active relationship like this for "Legend_Table[Cat] <-> Pollution_Data[NO2_Cat]":

Which means that the table is filtered down to just the NO2 catageories and Hex codes:

And also allows me to click an item in the table to filter down the map to just those categories (the map is using Pollution_Data):

However, I have a switch that lets users change between NO2 and NOX but I want to maintain the legend interactivity. To do this I create two inactive relationships for NO2 and NOX as so (note the screenshot is incorrect but both relationships are both ways):

Then I make a switch like this:

LegendSwitch = SWITCH([Selection],
1,CALCULATION(COUNT(Pollution_Data[NO2_Cat]),USERELATIONSHIP(LegendTable[CAT],Pollution_Data[NO2_Cat])),
2,CALCULATION(COUNT(Pollution_Data[NOX_Cat]),USERELATIONSHIP(LegendTable[CAT],Pollution_Data[NOX_Cat])))

Now this works for filtering the table (or legend, as its intended as in this case) so that NO2 and NOX selections result in different values:

However, when clicking a category in the legend table like before, the map no longer filters:

I thought that USERELATIONSHIP would make the relationships function in exactly the same way but just switch according to what was selected in the slicer. What am I missing here? Are there any other methods that can be used to achieve a similar outcome?

Thanks in advance,

Joe

[v-yanjiang-msft · Accepted solution]

Hi @Joe_Marangos ,

As the relationship only active in the measure calculation, NO2_Cat and NOX_Cat can't be filtered by the Legend_Table, they don't actually have a relationship.

For your purpose, the data model should be modified or create a new table.

Method1.

In Power Query, unpivot the NO2_Cat and NOX_Cat columns.

They'll be in one column, so it's easy to build relationships.

Method2.

Create a new table, separate NOX_Cat.

NOX_Cat Table = SELECTCOLUMNS('Pollution_Data',"NOX_Cat",'Pollution_Data'[NOX_Cat])

Both tables are related to Legend_Table.

This is an overall idea, the specific operation should be combined with your sample.

Best Regards,
Community Support Team _ kalyj

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

[MFelix]

Hi @Joe_Marangos ,

Is the chart also done based on the USERELATIONSHIP formula or is it based on a different one?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.