Forum Discussion

Joe_Marangos's avatar
Joe_Marangos
Frequent Visitor
4 years ago
Solved

USERELATIONSHIP not working as expected. Could use some suggestions.

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

 

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

2 Replies

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

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