Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
VTB
Frequent Visitor

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. 

 

DateValueTypeProduct
12/31/20150.05AWorking
12/31/20150.20BNot Working
12/31/20150.43C 
12/31/20150.38DWorking
12/31/20150.48ANot Working
12/31/20150.49B 
12/31/20150.86CWorking
12/31/20150.21DNot Working
12/31/20150.43AWorking
12/31/20150.80BNot Working
12/31/20150.77CWorking
12/31/20150.74DNot Working
12/31/20150.64FWorking
12/31/20150.10HWorking
12/31/20150.26JWorking
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

VTB
Frequent Visitor

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:

DateValueTypeProduct
12/31/20150.05AWorking
12/31/20150.20BNot Working
12/31/20150.43C 
12/31/20150.38DWorking
12/31/20150.48A

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. 

 

 

Anonymous
Not applicable

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
Frequent Visitor

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
Frequent Visitor

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.