How to set up slicer that shows related data by another column

[Anonymous]

Hi Everyone! 

I have a table Document, and I want to set up a slicer by Project Number that shows me in table related content by another column. Let me explain this by this example: 

Here we have column Document Cut = O-4720-J-5022. 

When I select Project number slicer CP-20-3006 it is neccessary to show not only Document Numbers that related to this CP-20-3006, but also need to show all document numbers which have the same value in Document Cut.  So all this Project Number values have the same Document Cut. 

I understand that in this case probably we should filter by Document Cut number, but  It's important to filter by Project Number and see the related content.

Is there any options how it can be realised? 

Appreciate for any help! 

[tamerj1 · Accepted solution]

Hi @Anonymous 

First create a slicer table for project number

Projects =
ALLNOBLANKROW ( 'Table'[Project Number] )

Then place the following measure in the filter pane of the table visual, select "is not blank" then apply the filter.

FilterMeasure =
VAR SelectedProjects =
    VALUES ( Projects[Project Number] )
VAR DocumentCuts =
    CALCULATETABLE (
        VALUES ( 'Table'[Document cut] ),
        'Table'[Project Number] IN SelectedProjects,
        ALL ( 'Table' )
    )
RETURN
    COUNTROWS ( FILTER ( 'Table', 'Table'[Cocument cut] IN DocumentCuts ) )

[Anonymous]

Hi @tamerj1 

Thank you so much for your feedback! It works for me. 

Sorry to bother, there is another case on this table, could you please advise? 🙂

CASE EXAMPLE: 

Here I filtered Project Number = CP-22-3145 and get all related columns. 

And I want to show via drill through Drawing Number, Project Number, Master Document columns on another table where values related to ALL Master Document values. 

Expected result will be(I filtered it manually by taking all Master Document values from previos screen): 

You can see here that this table includes not only CP-22-3145, but all Project Numbers and Drawing Numbers that have the same Master Document.