topic Re: Custom filter Measure with DAX in DAX Commands and Tips

Custom filter Measure with DAX

VTB — Sat, 06 Jul 2019 13:59:20 GMT

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

Re: Custom filter Measure with DAX

Anonymous — Sat, 06 Jul 2019 14:44:26 GMT

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

Re: Custom filter Measure with DAX

VTB — Sat, 06 Jul 2019 17:37:36 GMT

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.

Re: Custom filter Measure with DAX

Anonymous — Sat, 06 Jul 2019 18:23:25 GMT

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

Re: Custom filter Measure with DAX

VTB — Wed, 10 Jul 2019 18:39:55 GMT

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

Re: Custom filter Measure with DAX

VTB — Wed, 10 Jul 2019 18:57:43 GMT

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