Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Quenril
Helper I
Helper I

Populate table visual through a field not connected in the data model

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:Lookup.png

 Thank you so much

Quenril

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

vlinyulumsft_0-1718540239971.png

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:

vlinyulumsft_1-1718540274768.png

vlinyulumsft_2-1718540281393.png

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

vlinyulumsft_3-1718540318487.png

 

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.

View solution in original post

4 REPLIES 4
Quenril
Helper I
Helper I

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
Helper I
Helper I

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

 

 

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.

vlinyulumsft_0-1718540239971.png

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:

vlinyulumsft_1-1718540274768.png

vlinyulumsft_2-1718540281393.png

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

vlinyulumsft_3-1718540318487.png

 

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
Community Support
Community Support

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:

vlinyulumsft_0-1718076907146.png

vlinyulumsft_1-1718076921594.png

2.Next create relationships between tables:

vlinyulumsft_2-1718076943385.png

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.

vlinyulumsft_3-1718077013773.png

vlinyulumsft_4-1718077022812.png

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.