Show a table with all values except the selected in a slicer

[mlsx4]

Hello everyone,

I've got a slicer with all documents: Order 1, order 2, order 3...

Then I need two tables both showing Order ID and Manufacturing Cost

Firstly, Table A which works as usual: if I select Order 1, it will be filtered by Order 1. 

But then, I need a second Table B, which should work in an "inverse" way. I mean, if I select by Order 1, it must show all order except Order 1.

I've tried using a measure to keep the selected value and another one to filter Table B by it, disconnect the interaction...

OrderSelected = 
CALCULATE(
    SELECTEDVALUE('Order'[OrderID]),
    ALL('Order')        --Here I've tried ALLSELECTED ('Order') too
)

And then:

ShowOrder =
VAR Sel = [OrderSelected]
RETURN
    -- If there's no project → show all
    IF( ISBLANK(Sel), TRUE(),
        -- If there's a project selected→ show the others
        Order[OrderID] <> Sel
    )

However, up to now, I haven't been able to manage it. Any help would be aprecciated.

P.S. I have tried, in the second measure to return only Sel and if interaction is enabled it shows only Order 1 and if it is blocked it shows Blank()

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Kedar_Pande]

OrderSelected = SELECTEDVALUE('Order'[OrderID])

ShowOrderFlag =
VAR Sel = [OrderSelected]
RETURN IF(
ISBLANK(Sel),
1, // No selection → show all
IF('Order'[OrderID] <> Sel, 1, 0) // Show if NOT selected
)

Apply ShowOrderFlag = 1 as a visual level filter on Table B.

Set slicer interaction OFF for Table B to avoid double filtering.

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[mlsx4]

Thank you for your help, but as I have commented previously is impossible to do this line: 

IF('Order'[OrderID] <> Sel, 1, 0). It shows that it needs an aggregation

[grazitti_sapna]

Hi @mlsx4,

You can achieve this by creating a disconnected order table with Order Ids. either in Power Query or using DAX

Order_Slicer = VALUES(Order[OrderID])

Use values from disconnected table in Slicer

Now use this in the measure you already created

SelectedOrder =
SELECTEDVALUE(Order_Slicer[OrderID])

ShowOrder_B =
VAR Sel = [SelectedOrder]
RETURN
IF (
ISBLANK(Sel),
TRUE(), -- nothing selected -> show all
Order[OrderID] <> Sel -- invert filtering
)

apply this measure in table B's visal level filter and set to true.

If you want to use it in multiple selection use below DAX

ShowOrder_B =
VAR Selected =
VALUES(Order_Slicer[OrderID]) -- returns a table
RETURN
IF (
ISBLANK(SELECTEDVALUE(Order_Slicer[OrderID])),
TRUE(),
NOT Order[OrderID] IN Selected
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[mlsx4]

I've tried your measure in an easy example:

Order_Slicer is OK

But then, "Cannot find table 'Order_Slicer' error

And also in ShowOrder_B

Any idea?

[Anonymous]

Hi @mlsx4 ,

Your measure is referencing a table named Order_Slicer, but that table does not exist in your model.

In the screenshot on the right, I can see your real table names:

Ordenes

Importe

ResumenDiarioResumenDiario_02

(others…)

There is no table called Order_Slicer in the Fields pane.

Try below DAX.

1. Selected order measure

SelectedOrder =

SELECTEDVALUE(Ordenes[OrderID])

2. Show all except selected

ShowOtherOrders =

VAR sel = [SelectedOrder]

RETURN

    IF(

        ISBLANK(sel),

        TRUE(),

        Ordenes[OrderID] <> sel

    )

In the Filters pane for Table B:

drag ShowOtherOrders

set to Show items when value is TRUE

If my response as resolved your issue please mark it as solution and give kudos.

[grazitti_sapna]

Hi @mlsx4,

In the screenshots you are using disconnected table as table 2, but you should use your original table. Only the slicer should be used from disconnected table, can you share the sample .pbix file. i can help to create the solution

[mlsx4]

Example.pbix 

Check if you can download it

[Ashish_Mathur]

No file there.

[mlsx4]

Hi @Ashish_Mathur ,

Are you sure? I've tried the link in a private session in the explorer and it works for me. It is an attached file in OneDrive