Forum Discussion
Custom filter Measure with DAX
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 |
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
5 Replies
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
