Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
hi folks,
looking to see if someone handy with DAX can help me figure out if this is doable as a measure - or if i should just do this on the sql side, and feed the final result back into powerbi.
I have a table example with the following:
Where I'd like to almost create a new column that considers Type And Product -> as in give me a new set of values that filters out where Type = A or B, and happens to be Product = Not working, so that end result still shows all values for all types, including for A & B, where their Product value is either Null or Working.
Date | Value | Type | Product |
12/31/2015 | 0.05 | A | Working |
12/31/2015 | 0.20 | B | Not Working |
12/31/2015 | 0.43 | C | |
12/31/2015 | 0.38 | D | Working |
12/31/2015 | 0.48 | A | Not Working |
12/31/2015 | 0.49 | B | |
12/31/2015 | 0.86 | C | Working |
12/31/2015 | 0.21 | D | Not Working |
12/31/2015 | 0.43 | A | Working |
12/31/2015 | 0.80 | B | Not Working |
12/31/2015 | 0.77 | C | Working |
12/31/2015 | 0.74 | D | Not Working |
12/31/2015 | 0.64 | F | Working |
12/31/2015 | 0.10 | H | Working |
12/31/2015 | 0.26 | J | Working |
Solved! Go to Solution.
Hi.
Yeah, English can be hard but... I wouldn't complain. If you knew Polish, Russian, Czech, Hungarian or Chinese, then you'd know what it really means HARD 🙂
To the point. You can create a column in your table and put TRUE (or 1, or "Remove") if the conditions are met, FALSE (or 0, or "Keep") when not.
[Keep/Remove] := if ( 'Table'[Type] = "A" && 'Table'[Product] = "Not Working", "remove", "keep" )
If you want to AND several conditions, you can use &&. If you want to OR them, you use ||. Once you have the column defined above, you can create a new table that will only keep the rows with "keep" in them, thus effectively removing the ones where the condition is true. Just create a new table. Go to Modeling > Calculations > New Table and type this:
Filtered Table = FILTER( 'Table', 'Table'[Keep/Remove] = "keep" )
Best
Darek
Yeah....
If you try to formulate the requirements in a fashion that's understandable to a human being, then maybe someone will be able to help you 🙂 For the time being, it's completely obscure what you want.
Best
Darek
English can be hard...
let me try this again, am trying to see what dax functions would work best if I were to create either a New column, or New table + column, that would take existing set of columns and filter out a set of values in mulitple columns.
so in the below to simplify this:
Date | Value | Type | Product |
12/31/2015 | 0.05 | A | Working |
12/31/2015 | 0.20 | B | Not Working |
12/31/2015 | 0.43 | C | |
12/31/2015 | 0.38 | D | Working |
12/31/2015 | 0.48 | A | Not Working |
to filter the table such that you remove row where you have Type = A, Product = Not Working. (but leave Type = A, Product = Working)
My normal filter right now either removes anything that equals "A", or "Not working", which is an issue, when say you want to keep B around thats also "Not Working". So instead of creating another column that merges Type & Product into a single column and sorting on that, I was wondering if there are joint conditional filters that can be used as a dax measure/new table/new column.
Hi.
Yeah, English can be hard but... I wouldn't complain. If you knew Polish, Russian, Czech, Hungarian or Chinese, then you'd know what it really means HARD 🙂
To the point. You can create a column in your table and put TRUE (or 1, or "Remove") if the conditions are met, FALSE (or 0, or "Keep") when not.
[Keep/Remove] := if ( 'Table'[Type] = "A" && 'Table'[Product] = "Not Working", "remove", "keep" )
If you want to AND several conditions, you can use &&. If you want to OR them, you use ||. Once you have the column defined above, you can create a new table that will only keep the rows with "keep" in them, thus effectively removing the ones where the condition is true. Just create a new table. Go to Modeling > Calculations > New Table and type this:
Filtered Table = FILTER( 'Table', 'Table'[Keep/Remove] = "keep" )
Best
Darek
cool, this should do most of the trick.
is there a way to imbed like an array of values into IF statements?
so if the below needed to have "A", and "B", without having to write a nestled IF statements?
[Keep/Remove] :=
if (
'Table'[Type] = "A" && 'Table'[Product] = "Not Working",
"remove",
"keep"
)
actually just got it to work with SWITCH function! Where you can keep piling on conditions, so all good now!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |