Cross-Highlighting across different rows

[StillLearningMe]

I have a table that shows candidate journeys in a job interview process:

id

candID

attempt

enquiry date

enquiry job pref

interview date

interview job pref

assessment date

assessment job pref

join date

join pref

withdrawal date

withdrawal pref

1

100

1

1/01/2020

no preference

1/02/2020

chef

2/02/2020

chef

2

100

2

10/05/2020

baker

11/05/2020

baker

3

100

3

1/06/2020

teacher

1/07/2020

teacher

4

101

1

1/02/2020

chef

20/02/2020

chef

28/02/2020

chef

29/02/2020

chef

5

101

2

15/03/2020

driver

17/03/2020

driver

6

102

1

3/03/2020

no preference

5/03/2020

no preference

id 1 shows they made an enquiry with no job preference but then they attended an interview where they decided on a job preference. then they decided to withdraw.

id 2 shows the same candidate changing their mind and their job preference and going into an assessment for that job. they then failed the assessment and were thus withdrawn again. 

id 3 shows the same candidate trying for another job and making it through.

they don't need to enquire again, nor do an interview if they were successful at those stages. however if they want to try for a different job they need to do an assessment for that job. 

I want to create 5 bar graphs showing the interaction between different job preferences. Unfortunately it ends up looking like this:


Here I clicked on "no preference" in the "candID by enquiry job pref" visual.
I understand this is a row context thing but I just can't figure out how to get 1 row to interact with another row based on a unique key, like their candidate id (candID).

What I want is for "no preference" in the enquiry stage to result in "baker" and "teacher" in assessment stage increasing by 1. Join pref should increase by 1 for "teacher" and withdrawal should end up with 1 for "baker".

I don't want to fill in the blanks with the dates and preferences from the previous stages because that ends up in duplicates. Eg:


How do I make this happen like in the second screenshot but not showing the duplicates? Thank you 🙂

[Anonymous · Accepted solution]

Hi @StillLearningMe,

It sounds like you want to click on the detail records and extract the parent key to interact with other visuals, right? 

If that is the case, I think the basic filter effect should not be suitable for this requirement. Power bi will use current row content instead of specific field keys to interact with other visuals. (column1=a, column2=b,etc...)

For this scenario, I think you need to create a calculated table that copies from the fact table and does not has relationships to other tables.
Then you can write measure expression to extract the selections and compare with target tables records return tag and apply on 'visual level filter' to enable filter effects.

Deep dive into measure filters in Power BI Desktop - SQLBI

Regards,

Xiaoxin Sheng

[StillLearningMe]

thanks, you got me thinking about parent and foreign keys. i've now go the right key pairs in my tables and the correct data model setup and it's working great.