Retrieving a subset of a filter

[smpa01]

@AlexisOlson  I was trying to answer a Q yesterday (lost the link) and I came across this issue

I have two tables as following. There is no relationship between them. One is called Order (which is the fact table) and the second one is called prod (to be used to slice Order)

|               Order              |
|----------|----------|------------|
| Order no | Quantity | Product ID |
|----------|----------|------------|
| 1        | 1        | 1          |
| 1        | 2        | 2          |
| 1        | 3        | 3          |
| 2        | 4        | 1          |
| 2        | 5        | 2          |
| 3        | 6        | 1          |
| 3        | 7        | 2          |
| 3        | 5        | 4          |
| 4        | 4        | 2          |
| 5        | 3        | 3          |
| 6        | 2        | 4          |
| 7        | 1        | 2          |
| 7        | 3        | 3          |
| 8        | 4        | 3          |
| 8        | 5        | 4          |
| 9        | 6        | 1          |
| 9        | 7        | 2          |
| 10       | 8        | 2          |
| 10       | 9        | 3          |
| 10       | 10       | 4          |

|           prod         |
|------------|-----------|
| Product ID | Product   |
|------------|-----------|
| 1          | Product 1 |
| 2          | Product 2 |
| 3          | Product 3 |
| 4          | Product 4 |

On the viz, prod[Product Id] was to be used as slicer.

If prod[Product ID]=1

it shoud slice order[Product ID] (which is 1)

and

figure out what are the order[Order no] for that order[Product ID] -> picture below (1,2,3,9)

and 

produce a matrix  for everything but that prod[ProductID] (2,3,4) but for the same prod[Order no] (1,2,3,9)

So if I select 1, I should see the following matrix with order[Product Id] on Rows and order[Order no] in columns

I was thinking of a measure like following

Measure =
VAR _selectedProd =
    ALLSELECTED ( prod[Product ID] )
VAR _relatedProd =
    MAXX (
        FILTER ( 'order', 'order'[Product ID] IN _selectedProd ),
        'order'[Product ID]
    )
VAR _everythingButSelectedProd =
    CALCULATE (
        SUM ( 'order'[Quantity] ),
        FILTER ( 'order', NOT 'order'[Product ID] IN { _relatedProd } )
    )
VAR _relatedOrder =
    MAXX (
        FILTER ( 'order', 'order'[Product ID] IN _selectedProd ),
        'order'[Order no]
    )
VAR _combinedFilter =
    CALCULATE (
        SUM ( 'order'[Quantity] ),
        FILTER (
            'order',
            NOT 'order'[Product ID]
                IN { _relatedProd }
                && 'order'[Order no] IN { _relatedOrder }
        )
    )
RETURN
    _combinedFilter

But I could not make it to work. _combinedFilter removes ProductID altogether and the sum is wrong too.

How can the subset be filtered properly?

Thank you in advance.

[AlexisOlson · Accepted solution]

If you want your visual to show things that are not selected from your slicer, then you need the slicer and the visual to be using separate columns. In particular, I'd recommend creating a new slicer table as a copy of prod.

Use this new table for the slicer and the measure becomes mostly straightforward:

Measure1 = 
VAR _selectedProds = VALUES ( prodSlicer[Product ID] )
VAR _relatedOrders =
    CALCULATETABLE (
        VALUES ( 'order'[Order no] ),
        REMOVEFILTERS ( 'order' ),
        prod[Product ID] IN _selectedProds
    )
VAR _combinedFilter =
    CALCULATE (
        SUM ( 'order'[Quantity] ),
        NOT ( prod[Product ID] IN _selectedProds ),
        KEEPFILTERS ( 'order'[Order no] IN _relatedOrders )
    )
RETURN
    _combinedFilter

[smpa01]

@AlexisOlson @CNENFRNL  many thanks !!!

[CNENFRNL · Accepted solution]

I saw many tricky ideas by some rookies; but I admit that I gained quite some experience by dealing with those weird issues.😂

[AlexisOlson]

Ah, you solved it by copying [Product Name] to the Order table. I was confused at first how you managed to pull it off without another table involved. No new table, just a new column.

[AlexisOlson · Accepted solution]

If you want your visual to show things that are not selected from your slicer, then you need the slicer and the visual to be using separate columns. In particular, I'd recommend creating a new slicer table as a copy of prod.

Use this new table for the slicer and the measure becomes mostly straightforward:

Measure1 = 
VAR _selectedProds = VALUES ( prodSlicer[Product ID] )
VAR _relatedOrders =
    CALCULATETABLE (
        VALUES ( 'order'[Order no] ),
        REMOVEFILTERS ( 'order' ),
        prod[Product ID] IN _selectedProds
    )
VAR _combinedFilter =
    CALCULATE (
        SUM ( 'order'[Quantity] ),
        NOT ( prod[Product ID] IN _selectedProds ),
        KEEPFILTERS ( 'order'[Order no] IN _relatedOrders )
    )
RETURN
    _combinedFilter