Filter by Slicer and freeze all values within same group based on column

[user35131]

I would like to filter by slicer from Book column and show all books that correspond to the same book series of selected "Book". Is it also possible to highlight the one specific selection but show all books as well?

Hi i have a dataset that looks like this

Book Series	Book
Harry Potter	Harry Potter and the Sorcerer’s Stone
Harry Potter	Harry Potter and the Chamber of Secrets
Harry Potter	Harry Potter and the Prisoner of Azkaban
Harry Potter	Harry Potter and the Goblet of Fire
Harry Potter	Harry Potter and the Order of the Phoenix
Harry Potter	Harry Potter and the Half-Blood Prince
Harry Potter	Harry Potter and the Deathly Hallows
Game of Thrones	A Game of Thrones
Game of Thrones	A Clash of Kings
Game of Thrones	A Storm of Swords
Game of Thrones	A Feast for Crows
Game of Thrones	A Dance with Dragons
Twilight	Twilight
Twilight	New Moon
Twilight	Eclipse
Twilight	Breaking Dawn
Twilight	Midnight Sun

If I were to use a slicer based on column Book Series and say select Twilight, I would have a table that shows 

Book Series	Books
Twilight	Twilight
Twilight	New Moon
Twilight	Eclipse
Twilight	Breaking Dawn
Twilight	Midnight Sun

If i were to use slicer based on column Book and selected new moon my table would look like this

Book Series	Book
Twilight	New Moon

Is there a way to write a measure that if i were to select say New Moon or any book in Twilight Book Series in slicer, my table would show all books within corresponding "Book Series" column as well as highlight the selected to compare to other books?

Book Series
Twilight	Twilight
Twilight	New Moon
Twilight	Eclipse
Twiligh	Breaking Dawn
Twilight	Midnight Sun

[speedramps]

Hi again user35131 

Can you hide any confindential data and save the PBIX to OneDrive or Drop box.
Right click on the file to give evereyone read access and then copy and paste the link into this chat.

We can then replicate the error, see why it is going wrong and help you fix it.

[v-yiruan-msft]

Hi @user35131 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a book dimension table with below formula and use the book field as slicer selections

Books = VALUES('Table'[Book])

2. Create a measure as below to judge whether display the books with same series

Flag = 
VAR _selbook =
    SELECTEDVALUE ( 'Books'[Book] )
VAR _bookseries =
    CALCULATE (
        MAX ( 'Table'[Book Series] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Book] = _selbook )
    )
RETURN
    IF (
        NOT ( ISFILTERED ( 'Books'[Book] ) )
            || SELECTEDVALUE ( 'Table'[Book Series] ) = _bookseries,
        1,
        0
    )

3. Create a table visual with fields from your fact book table and apply the visual level filter with condition (Flag=1) just as shown in below screenshot

If the above one is not working in your scenario, please provide more sample data and your expected result with backend logic and special examples. Thank you.

Best Regards

[speedramps · Accepted solution]

Please consider this solution and hit that thumbs up button.

Click here to download a PBIX 

Note a silcer does what it says on tin.  It slices your data!
So you need 2 duplicate tables. A pick list and a library list.
Otherwise you will be head scratching trying to display the data that is hidden by the slicer!

Then drag the picklist to the slicer and the library list to the report.

Creates measures to get and display the picked book and series

• Picked book = SELECTEDVALUE(Picklist[Book])
• Picked series = SELECTEDVALUE(Picklist[Series])

Create a measures for conditional colour formating:-

Series colour =

VAR librarybook = SELECTEDVALUE('Library'[Book])

RETURN

IF(librarybook = [Picked book], "RED")

Creates a measures to indicate if the series should be shown or hidden

Series indicator =

VAR library_series = SELECTEDVALUE('Library'[Series])

RETURN

IF(library_series = [Picked series], 1)

[user35131]

How you linking the tables to the measure becasue when i see in the values section its only the columns

Also is that possible by bringing all measures to the aggregate measure table? Not sure how to do that and if that is key.

[speedramps]

Hi again user35131

It sounds like you are a Power BI begineer, so here are some tips which you may or may not already know....

1) In the "Values" well try hover on "Book" or "Series" and a pop-up will display the table[field].

2) On the far right menu if you click the ">" next to Dax, Library of Picklist then it will expand to show all the fields in the table.

3) If you click on any measures in the Dax tables, then on the very far left of the top menu bar you can change the Home table.  The measure will work in any table. Howevere, I prefer to group all my measures in the Dax table.

4) If you click on the Model icon (third icon down on the far left bar) you will see that there are no relationships.  As I tried to explain before a slicer does what it says on the tin. It slices!! If you built relationships then it would slice relations, and would not be able to show other books in the series. So you need to use detatched tables in this scenario. Hope you understand that now?? 

5) You dont need replationships ....

You need 2 duplicate tables. A Picklist and a Library list.
Otherwise you will be head scratching trying to display the data that is hidden by the slicer!

Then drag the picklist to the slicer and the library list to the report.

Creates measures to get and display the picked book and series

• Picked book = SELECTEDVALUE(Picklist[Book])
• Picked series = SELECTEDVALUE(Picklist[Series])

Create a measures for conditional colour formating:-

Series colour =

VAR librarybook = SELECTEDVALUE('Library'[Book])

RETURN

IF(librarybook = [Picked book], "RED")

Creates a measures to indicate if the series should be shown or hidden

Series indicator =

VAR library_series = SELECTEDVALUE('Library'[Series])

RETURN

IF(library_series = [Picked series], 1)

6) To link the colours .....
Click on "Your report".
In the Values well, right click on Series > Conditional formatting > Fpnt colour > Format by = Filed value > Based on = Series colour

7) To link the indicator ...
Click on "Your report".
Click "<" above the far right Filters bar, to expand the Filters menu.

Drag the Series indicator to the Filter menu
and show items when the value = is not blank

[user35131]

I don't know why it works partially for my dataset. For example if i click sorcerer of stone it shows all 7, but when i click prisoner of azkaban it'll show 6 of 7.