Forum Discussion
Filter rows by value in another rows with DAX
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!
// 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 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.⚡
7 Replies
// 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 )Thank you Anonymous , it works.
And special thanks for detailed explanation and useful advise.
- parry2k
Super User
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.⚡
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 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.⚡
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!
