Creating a filter that keeps & counts all other records for the same ID

[sam-biomodal]

Hey all, I'm hoping that someone can help me create a filter that can help me do dynamic cross-referencing:

My data structure is like this:

ID	Category
01	Blue
01	Green
01	Black
02	Blue
02	Red
03	Yellow
03	Red
03	Blue
03	White

What I am trying to get is a dynamic report where if I select a category, i.e. Red, I will be able to see what other categories are present (and count them) for IDs that also had red as a category. In the example above, IDs 2 & 3 have Red, so 2 & 3 would be returned and I'll be able to then do a count on the remaining categories, i.e. blue = 2, yellow = 1, white = 1

Ideally, I would have the filter category as a slicer widget and then show the count of categories in a bar chart.

Any help would be greatly appreciated, thanks!

[Anonymous · Accepted solution]

Hi,@sam-biomodal 

Regarding the issue you raised, my solution is as follows:
1. Create a table with the same values as the original table as a virtual table, the name of the original table is "cross-referencing" and the name of the virtual table is "Table". Select the value of the "Table" table as the slicer.

2. Based on my understanding of your requirements, I have created the following measure:

Measure = 
VAR color=SELECTEDVALUE ('Table'[Category])
VAR ID1 =
    CALCULATETABLE (
        VALUES ( 'cross-referencing'[ID]),
        FILTER ( ALLSELECTED ( 'cross-referencing' ), 'cross-referencing'[Category] = color )
    )
RETURN
    CALCULATE (
        COUNT ( 'cross-referencing'[ID]),
        FILTER ( 'cross-referencing', 'cross-referencing'[ID] IN ID1 && 'cross-referencing'[Category]<> color )
    )

3.Change the visualization from a table to a bar chart and change the position of the values to measure:

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

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.