Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
LATH
Frequent Visitor

Filter a Slicer by Table Selection

Hello, 

I have a Report Page that displays furniture as a table, with multiple other tables as their Slicer. However, there is a request from the Business Side to be able to select specific rows (easily) and export only those selected Items. 

All information is coming from a single Excel Document.

 

I have found two options.

First copy the underlying table, create a Slicer on the Canvas (1) which is linked to another page, that only shows the selected rows. This works, however the work itself for the business is tedious, since they need to search the slicer for a specific String.

LATH_0-1738665314253.png

So my Idea would be that a selection in the table itself limits the options in the slicer.

 

In my second try I again worked with the copied data. However, this time I inserted a small table with all the required data, which is filtered by the selection in the other table, however one miss click and all the selection has to be done again.

 

So I tried to use the following Dax Code to filter the Slicer of the first try, but I am not getting the right result.

Measure to find the selected Rows:

SelectedValuesMeasure = 
    VAR SelectedColumns = VALUES(OfflineExcel[Kombi3])
    Return CONCATENATEX(
        SelectedColumns,
        OfflineExcel[Kombi3]
        ,", "
    )

And the Column measures to be able to filter the slicer:

IsSelected = 
IF(
    CONTAINSSTRING(
        [SelectedValuesMeasure],
        Silicer_Table[Kombi3]
    ),
    1,
    0
)

 

3 REPLIES 3
LATH
Frequent Visitor

Another Option, that I thought of, was to access and reverse the Exclude function of the table: 

LATH_2-1738736422728.png

However, I do not know if I can a Filter of a Visual on another table:

LATH_3-1738736479072.png

 

DataNinja777
Super User
Super User

Hi @LATH 

 

Your approach is logical, but the issue arises from how Power BI handles context when selecting rows in a table and filtering a slicer. Measures are evaluated dynamically and do not persist selections across different visuals, which is why your slicer filtering is not working as expected. Instead of relying on a measure alone, you can use a disconnected slicer table and a filtering measure that responds to the table selection.

First, create a distinct list of values from your main table that will serve as the slicer options. You can do this with the following DAX formula:

Slicer_Table = DISTINCT(OfflineExcel[Kombi3])

Next, modify the measure that captures the selected values from the table dynamically. This measure ensures that only the selected items from the main table are concatenated into a string:

SelectedValuesMeasure =
VAR SelectedItems = VALUES(OfflineExcel[Kombi3])
RETURN 
IF(
    NOT ISEMPTY(SelectedItems),
    CONCATENATEX(SelectedItems, OfflineExcel[Kombi3], ", "),
    BLANK()
)

Now, create a measure that will filter the slicer based on what is selected in the table. This measure checks if the value in the slicer exists in the selected rows of the table and returns 1 if it does:

IsSelected =
IF(
    SELECTEDVALUE(Slicer_Table[Kombi3]) IN VALUES(OfflineExcel[Kombi3]),
    1,
    0
)

Once you have this measure, apply it as a visual-level filter to the slicer and set it to show only values where IsSelected equals 1. This way, the slicer dynamically updates based on the selected rows in the table, reducing the manual effort of searching through slicer options. When a row is clicked, the slicer will only display the relevant selections, making it easier for users to filter and export the desired data efficiently. If no selection is made, the slicer will revert to showing all available options. Let me know if you need further refinements!

 

Best regards,

Hello @DataNinja777

thanks for the rapid response. However, It seems that either I am doing something wrong, or that the solution doesn't do the intended filtering(, probably the first option :D).

 

So I did that: 

LATH_0-1738735964593.png

And that: 

LATH_1-1738735979936.png

 

However, the filter does not change a bit when I select Values: My code looks the following way:

Slicer_Table = DISTINCT(OfflineExcel[Artikel-Nr./AAF])

I changed the Name, since the Kombi3 is a wired String.

SelectedValuesMeasure_Test = 
    VAR SelectedItems = VALUES(OfflineExcel[Artikel-Nr./AAF])
    RETURN
        IF(
            NOT ISEMPTY(SelectedItems),
            CONCATENATEX(SelectedItems, OfflineExcel[Artikel-Nr./AAF], ", "),
            BLANK()
        )

And Since in your example, you did not use this measure I implemented it in the last bit, but also it your Way.

IsSelected = 
IF([SelectedValuesMeasure_Test] IN VALUES(OfflineExcel[Artikel-Nr./AAF]),
1,
0)

I also did try to create a relationship between both tables, but neither with nor without seemed to work for me. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.