Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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:
@Gerry_PBI is this what you are looking for?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
// 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |