Use selection to filter table, use associated result to filter separate table visual

[CvanP]

Hi there,

I got the following data:

authorisation_id	role_id	identity_id
1	1002	1
1	1002	2
1		3
1		4
1		5
2	1003	3
2	1003	4
3	1004	5
4	1005	1
4	1005	2
4	1005	3
4	1005	4
4	1005	5
4	1005	6
4	1005	7

Now I want to do the following:

1. Select an authorisation_id, for example: 2
2. Take the resulting identity_id's: 3 & 4
3. Show a table of all the roles these identity_id's (3 & 4) have, so in this case role_id's: 1003 and 1005

The problem I am running into, is that when I select an authorisation_id in a table in my report, it automatically filters role_id as well. I know I can prevent this by going to format -> edit interactions -> None on my role_id table visual. But this gives me all the role_id's, even the ones not associated with the identity_id's that have the selected authorisation_id. I tried solving this with some dax with calculated measures/columns so it gives a TRUE/FALSE based on if the role_id has one of the selected identity_id's, but I just can't make sense of it and neither can CoPilot etc.

So I am looking for a way to answer this question:
How can I show all the role_id's associated with the identity_id's that have the current selected authorisation_id.

Any help is appreciated and if you need any more info please let me know.

[CvanP · Accepted solution]

Thanks for the help everyone.

In the end I solved it the following way:

1. Made a copy of my table using DAX:
   CopyDemoData = DemoData
2. Kept this new table CopyDemoData separate from DemoData (so no relationships)
3. Added DAX:
   
   
   CountSelectedUsers =
   VAR __selectedUsers = VALUES(DemoData[identity_id])
   RETURN
   COUNTROWS(__selectedUsers)
   
   

   SelectedUsers =

   VAR __selectedUsers = VALUES(DemoData[identity_id])

   RETURN

   CONCATENATEX(DemoData,DemoData[identity_id], ",")
   
   

   IdentitiesInRole =

   CONCATENATEX(VALUES('Table'[identity_id]), 'Table'[identity_id], ",")
   
   

   CompareValues =

   IF(

       COUNTROWS(

           INTERSECT(

               VALUES(DemoData[identity_id]),

               VALUES('Table'[identity_id])

           )

       ) > 0,

       "Found",

       "Not Found"

   )
   

   CountCompareValues =

       COUNTROWS(

           INTERSECT(

               VALUES(DemoData[identity_id]),

               VALUES('Table'[identity_id])

           )

       )
4. Filtered based on CompareValues
5. Celebrated 

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[parry2k]

@CvanP check this video on my channel that talks about something similar, tweak the solution as you see fit.

https://www.youtube.com/watch?v=zS1IDl3DLak&feature=youtu.be