Get table visual to filter data via a relationship not in the data model

[Quenril]

Hello,

I am seeking some help. I'm working with recruitment data and building dashboards for the line managers to see the activity and performance levels. In my dashboard I have a slicers from my 'Consultant lookup' table which can be used to drill down to Division, Team and Consultant levels.

In my data model, all of the [Consultant Name] fields connect to the person who owns the activity. For the 'Live Jobs' table, this is the column called [Job Owner]. Now my problem. For one table, I need the Consultant slicer to filter on a different column called [Referred by], instead of the column [Consultant Name] which is the connection in the data model. Please see the image below.

 Please can someone help? Is there any way to set up a secondary relationship in the data model?

Thank you so much

Quenril

[Quenril · Accepted solution]

Hello parry2k,

Thank you for your response. I have linked the [Consultant Name] in my 'Consultant lookup' table with the [Referred by] column of my 'Live Jobs' table via na inactive relationship. I have then created the measure:

Count Rows =

CALCULATE( COUNTROWS('Live Jobs'), USERELATIONSHIP('Live Jobs'[Referred by], 'Consultant lookup'[Consultant Name]))

When I pull this measure into a table visual, it is giving me a count, when I want it to return the name of the Consultant in the [Referred by] column. Is there any way to amend this please?

Thank you

Quenril

[parry2k]

@Quenril you can create a relationship with a reference column with consultant column which will be inactive, add a measure, and then use it as a visual level filter:

Row Count = 
CALCULATE ( COUNTROWS ( Youtable ), USERELATIONSHIP ( YourTable[Referece Column], ConsultantTable[Consultant Column ) ) 

[Quenril]

Hi parry2k,

Thank you for the quick response. 95% of my measures and other data visuals uses the 'Consultant Name' relationship that I have set up in the data model currently. I believe this is the only table which ideally requires a different relationship... Have you got any clever ideas? 😉

Thank you

Quenril

[parry2k]

@Quenril just wondering why not change the relation between consultant table to referred by column instead of having a relationship with consultant name?