Forum Discussion

VTB's avatar
VTB
Frequent Visitor
7 years ago
Solved

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. ...
DAX filter formula
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

VTB's avatar
VTB
Frequent Visitor
7 years ago

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"
)

 

VTB's avatar
VTB
Frequent Visitor
7 years ago

actually just got it to work with SWITCH function! Where you can keep piling on conditions, so all good now!