Filter rows by value in another rows with DAX

[Gerry_PBI]

Hello, community!
I've spent a few hours on it without success, so I desperately need your help 🙂

This is my sample source dataset:

id	col1
1	a
1	a
1	b
2	b
2	b
2	c
2	d
3	d
4	d
4	f
5	g
5	h

I want to filter id by col1 in this way: I want to see only these id's, for which there are no rows with selected col1 value.

For instance, if I selected b in the  col1 column I should see only id's 3,4,5  (1 and 2 id's should be filtered out because there are rows with these id's and b in col1)

So I want to see this:

Similarly, if I click f on the left visual (which is just a table visual with col1 column from my source dataset) i should see 1,2,3,5 id's and 4 should be filtered out.
So I am trying to create a DAX table (or measure) for the right visual.

I've tried different approached with DAX tables, this is my last one:

EXCEPT(
DISTINCT(SELECTCOLUMNS(CALCULATETABLE(sheet1,all(Sheet1[col1])),"id",Sheet1[id]))
,
summarizecolumns(
Sheet1[id]
))
 - but it returns an empty dataset, seems like the reason is removing filters with function ALL doesn't work in this scenario.

I am also using PBIRS PBI Desktop version, so there is no many-to-many relationship available.

Thanks a lot for your help!

[Anonymous · Accepted solution]

// To pull this off you have to
// have 2 separate dimensions:
// 1. Table called ID that will just
// have the unique ids from the table
// shown and
// 2. Table Col that will store the
// unique values from the col1 column
// in the table shown. Then you'll
// connect the dimensions to the table
// shown on the corresponding fields and
// you'll hide this fact table (call it FT). You
// should never drop columns from fact
// tables on the canvas (the only exception
// being when you debug). One visual on
// the canvass will hold a column
// from the ID dimension, and the
// other will hold the column from
// the Col dimension. Then you'll
// create this measure and will filter
// the visual with ID[id] via the
// Filter Pane
// (filter: where [Display ID?] = 1):

[Display ID?] =
IF( ISINSCOPE( ID[id] ),
    var ComplementIDs =
        EXCEPT(
            ALL( ID[id] ),
            CALCULATETABLE(
                SUMMARIZE(
                    FT,
                    ID[id]
                ),
                ALL( ID )
            )
        )
    var Result =
        SELECTEDVALUE( ID[id] )
            in ComplementIDs
    return
        1 * Result
)

[parry2k · Accepted solution]

@Gerry_PBI maybe I didn't save the file. check new attachment.

✨ Follow us on LinkedIn

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Gerry_PBI]

It also works, thank you!
I see you guys have similar approach (using measure and filters instead of trying to create filtered dax table)
It makes sense. Thanks a lot!

[parry2k]

@Gerry_PBI if that is the result you want, see attached file, just focus on two tables in the model (Table and Table for Slicer )  and a measure to filter on the selection.  Ignore other tables in the file.

✨ Follow us on LinkedIn

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Gerry_PBI]

Hello pary2k,

>just focus on two tables in the model (Table and Table for Slicer )  and a measure to filter on the selection. 

I don't see Table for Slicer and a measure in this file, the only relevant data source is "Table" which is the raw data:

[parry2k]

@Gerry_PBI is this what you are looking for?

[Anonymous · Accepted solution]

// To pull this off you have to
// have 2 separate dimensions:
// 1. Table called ID that will just
// have the unique ids from the table
// shown and
// 2. Table Col that will store the
// unique values from the col1 column
// in the table shown. Then you'll
// connect the dimensions to the table
// shown on the corresponding fields and
// you'll hide this fact table (call it FT). You
// should never drop columns from fact
// tables on the canvas (the only exception
// being when you debug). One visual on
// the canvass will hold a column
// from the ID dimension, and the
// other will hold the column from
// the Col dimension. Then you'll
// create this measure and will filter
// the visual with ID[id] via the
// Filter Pane
// (filter: where [Display ID?] = 1):

[Display ID?] =
IF( ISINSCOPE( ID[id] ),
    var ComplementIDs =
        EXCEPT(
            ALL( ID[id] ),
            CALCULATETABLE(
                SUMMARIZE(
                    FT,
                    ID[id]
                ),
                ALL( ID )
            )
        )
    var Result =
        SELECTEDVALUE( ID[id] )
            in ComplementIDs
    return
        1 * Result
)

[Gerry_PBI]

Thank you @Anonymous , it works.
And special thanks for detailed explanation and useful advise.