topic Re: FILTER function with a List as filter condition in DAX Commands and Tips

FILTER function with a List as filter condition

Anonymous — Thu, 07 Jul 2022 09:36:02 GMT

Hello everyone!

I have a customer who does a lot of different filterings, i.e. for Article groups and subgroups, location groups and subgroups, including changing overlap and changing groups. To have one measure to sum them up, I thought I would work with a variable list in the filters, like this:

msrQuantity = VAR ItemList = "12345, 54321" RETURN CALCULATE( SUM(InventoryData[QTY]), FILTER( InventoryData, InventoryData[ItemNumber] IN {ItemList} ) )

Ultimately, the ItemList would be a CONCATENATEX-Function, and there will be additional conditions added.

The function only returns data if the list is a single value (i.e. were it only "12345", I get the right item. I also tried putting them further into brackets like "{12345}, {54321}", but that doesn't change the table, either.

Is this possible, or are there better ways to achieve this?

Re: FILTER function with a List as filter condition

tamerj1 — Thu, 07 Jul 2022 09:40:29 GMT

Hi Anonymous
lease use

msrQuantity = VAR ItemList = { "12345", "54321" } RETURN CALCULATE ( SUM ( InventoryData[QTY] ), FILTER ( InventoryData, InventoryData[ItemNumber] IN ItemList ) )

Re: FILTER function with a List as filter condition

Anonymous — Thu, 07 Jul 2022 09:41:26 GMT

Just a second after I post this question I see I formatted the list wrong, each value needs to be in quotation marks, and not the whole string, like {"12345", "54321"}. The filter condition works that way, too. But then the question is how can I build such a list string i.e. using CONCATENATEX?

Re: FILTER function with a List as filter condition

Anonymous — Thu, 07 Jul 2022 09:42:30 GMT

Thank you! I'm feeling really stupid right now, I saw that a minute after I made this post. Do you have any idea how I would automatically generate such a ItemList-String variably, though? I.e. using a CONCATENATEX?

Re: FILTER function with a List as filter condition

tamerj1 — Thu, 07 Jul 2022 09:47:03 GMT

Anonymous
Generate it out of what? What is the input? Also CNCATENATEX won't help you, you need a table not a scallar concatenated string value

Re: FILTER function with a List as filter condition

Anonymous — Thu, 07 Jul 2022 09:54:34 GMT

Thank you so much for your help. Keeping it simple, it would be a dimProduct table that is filtered many different ways in i.a. a table visual with filter visuals next to it, so my initial thought would be replacing the List string with

VAR ItemList = CONCATENATEX(dimProduct, dimProduct[ItemNumber], ",")

But as you say, I don't get the right thing out of this. CONCATENATEX isn't the right function. How do I get there, though?

Re: FILTER function with a List as filter condition

tamerj1 — Thu, 07 Jul 2022 09:57:29 GMT

Anonymous

Simply

VAR ItemList = VALUES(dimProduct[ItemNumber)