Populate table visual through a field not connected 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]. This connection in the data model works for 99% of my measures and visuals. Now the problem... For just one table, I need the Consultant slicer to filter on a different column called [Referred by] which is only an inactive connection in the data model. Please can someone help? Is there any way to set up a secondary relationship in the data model? In the image below I need the Consultant slicer to filter on the [Referred by] column instead of the [Consultant Name] column:

 Thank you so much

Quenril

[Quenril]

Hi Leroy,

Thank you for your message. Sadly I can't get this to work in the way I was hoping, however based on your explanation of the ways slicers work, this sounds like too advanced a measure for me to try right now, so I'll reset my expectations and perhaps engage a specialist for this function. Thank you so much for your help.

Best regards

Quenril  

[Quenril]

Hello Leroy,

Thank you so much for this. The outcome is exactly what I am after, however the only difficulty I have is that the combination of the Consultant who owns the job and the Consultant who referred the job changes every time, so I can't create a table with those items listed as they are dynamic. Can you think of any other way to achieve the same outcome?

I have uploaded here some sample data. So for the table called 'Live Roles', when I select the Consultant Name in the slicer, I need it to filter on column G 'Referred by' instead of column B 'Consultant Name'.... 

Live Roles table - Live Jobs SAMPLE DATA.xlsx

Consultant lookup table - Consultant lookup SAMPLE DATA.xlsx

Thank you

Quenril

[v-linyulu-msft · Accepted solution]

Hi,@Quenril 

If you want to keep the original slicer Settings, but want it to affect another column, in addition to my new table as a slicer, the only way I can provide now is to break the relationship between the two tables or directly change the inactive relationship to the active relationship, because the functionality of the slicer always can only filter the column that created the slicer or the column that has a relationship with his table, as I did before The table affected by the slicer is replaced with another table, and after obtaining the slicer value with the degree value, another column of the original table is filtered.

1.Here are my updated measure based on the data you provided, disconnecting all relationships:

REFERED = 
VAR CC=SELECTEDVALUE('consultant lookup'[Consultant Name])
RETURN
IF(ISFILTERED('consultant lookup'[Consultant Name]),
CALCULATE(MAX('live job'[Referred by]),FILTER('live job','live job'[Referred by]=CC)),MAX('live job'[Referred by]))

2.Of course, you can achieve the same result if you choose to change the inactive relationship to the active one as shown below:

3.Here's my final result, which I hope meets your requirements.

4.Here is the relevant documentation:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[v-linyulu-msft]

Hi,@Quenril 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Next create relationships between tables:

3.Create calculated column references:

Table 2 = SELECTCOLUMNS('Consultant lookup',"1",'Consultant lookup'[Consultant Name])

This new column is then used as a slicer.

4. Below are the measure I've created for your needs:

REFERED = 
VAR CC=SELECTEDVALUE('Table 2'[Consultant Name])
RETURN
IF(ISFILTERED('Table 2'[Consultant Name]),
CALCULATE(MAX('Table'[Referred by]),FILTER(ALLSELECTED('Table'),'Table'[Referred by]=CC)),MAX('Table'[Referred by]))

5.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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