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
Nurzh_17
Frequent Visitor

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

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: 

Nurzh_17_1-1682403543149.png

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! 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Nurzh_17 

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 ) )

 

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @Nurzh_17 

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 ) )

 

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. 

Nurzh_17_0-1682511514953.png

 

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): 

Nurzh_17_1-1682511514503.png

 

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.

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.

Top Solution Authors
Top Kudoed Authors