topic Re: Filter for multiple values in one cell in DAX Commands and Tips

Filter for multiple values in one cell

Anonymous — Wed, 29 Jun 2022 08:54:12 GMT

Hello,

i have a data model in which the main table sometimes includes columns in which several values are included in one cell to minimize the numer of rows and columns needed.

e.g.

Risks	IT-Protection Goals
G0.1; G0.2; G0.45; G036	C; I; A
G0.8; G0.1; G0.2;	C; A
...	I

I know want PowerBI to detect e.g. G0.2 in one of the cells and not view the content of the cell as one value. I know that I could split the data into several columns but I specifially want to avoid that and put all the individual values into one cell.

Help of any kind would be highly appreciated.

Best

Justus

Re: Filter for multiple values in one cell

tamerj1 — Wed, 29 Jun 2022 09:37:13 GMT

Anonymous
The idea is applicable and can be achieved. Please provide an example of what exactly are you trying to achieve?

Re: Filter for multiple values in one cell

Anonymous — Wed, 29 Jun 2022 09:57:04 GMT

Thx for your resposne. I'm e.g. trying to display the top5 risks. Another example would be to give a more detailed view on the risks by connecting another table where each risk is listed with more dteailed information. In general I just want to work with the individual values, use them in visuals, ect.

Re: Filter for multiple values in one cell

tamerj1 — Wed, 29 Jun 2022 13:45:21 GMT

Hi Anonymous
Sorry for the late reply. Here is a sample file for your reference https://we.tl/t-ROzVPTGfiW

The first thing to to is to extract the unique values of each column:

Then the rest would be simple. Here are some examples of what can be calculated.

Please provide more realistic sample of data and advise exactly what is required in order to support you further. Thank you

Re: Filter for multiple values in one cell

Anonymous — Tue, 26 Jul 2022 09:15:53 GMT

Hi Anonymous ，

Has this question been solved? If tamerj1's answer is helpful, please consider marking his answer as a solution, which will help more people find the answer faster. Thanks in advance!😊

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Re: Filter for multiple values in one cell

KubenM — Fri, 03 Feb 2023 10:35:06 GMT

Hello

I have a similar challenge. I am trying to stay away from complex DAX.
I have a filter setup for Increment that uses a Fact Table that has the following individual values defined:

Y23PI1

Y23PI2

Y23PI3

Y23PI4

My dimension table reflects that some rows have mulitple Increments in the same cell for example: Y23PI1, Y23PI2 or Y23PI2, Y23PI3, Y24PI4

As my filter only references single Increments, when I filter on Y23PI1 or Y23PI2, Power Bi returns zero as it can't find the single Increments.

Please assist if possible.

Kind Regards

Re: Filter for multiple values in one cell

tamerj1 — Fri, 03 Feb 2023 11:04:42 GMT

KubenM
I don't beleive there is any solution other than DAX. However it is not as complex as you might think. Please refer to attached sample file with the proposed solution

Filter Measure = VAR SelectedValues = VALUES ( FilterTable[Item Value] ) VAR String = SELECTEDVALUE ( 'Table'[Item Values] ) VAR Items = SUBSTITUTE ( String, ", ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) RETURN COUNTROWS ( INTERSECT ( T2, SelectedValues ) )

Re: Filter for multiple values in one cell

KubenM — Fri, 03 Feb 2023 13:14:50 GMT

Thank you so much for your solution to my problem. This is absolutely brilliant and is exactly what i required. Much appreciated. WoW.

Re: Filter for multiple values in one cell

KubenM — Wed, 08 Feb 2023 07:18:57 GMT

Hello tamerj1

I applied the DAX to the Matrix and the filtering works perfectly. I now have a challenge with applying the same DAX to the Pie charts and Bar Charts that are on the same Report. I am not able to filter these visuals by Increment.

Would you be able to add another column to the data table and add for example # of Sales and create the two visuals and apply the Filter Measure to these visuals so that I can follow your example.

And is there a way to sum each increment on the Pie chart when I filter on for example Y23PI1 so that the PIE chart shows a complete PIE as a sum of the vaules(# of Sales) for Increment Y23PI1 and not show me Y23PI1 in muliple slices of the PIE based on how the Item Values are distributed. When I select Y23PI1 and Y23P2, I would like the PIE chart to show two slices ie one slice for each Increment. I hope that makes sense.

Much appreciated.

Re: Filter for multiple values in one cell

tamerj1 — Wed, 08 Feb 2023 07:13:39 GMT

Hi KubenM
Seems to be working just fine!

Re: Filter for multiple values in one cell

KubenM — Wed, 08 Feb 2023 07:39:25 GMT

Hello Again

Thank you for your speedy response.

With reference to the PIE chart in the screen shot below, and the selection of Filter option Y23PI1 on the filter, I would like the PIE Chart to show me the count of 1 and the legend to show Y23PI1 and not 3 slices of the pie.

Re: Filter for multiple values in one cell

KubenM — Wed, 08 Feb 2023 07:33:54 GMT

The bar chart should also only show the values for one Increment per bar.

Re: Filter for multiple values in one cell

tamerj1 — Wed, 08 Feb 2023 07:46:58 GMT

KubenM
Why the count should be 1 not 3? And for the bar chart, what do you mean by "one increment per bar"?

Re: Filter for multiple values in one cell

KubenM — Wed, 08 Feb 2023 08:19:44 GMT

Here's what I am trying to achieve with Product Increments using the SAFe Methodology:

In JIRA we have a number of Business Epics that Teams plan in each PI by capturing in the Fixed Version field on the Business Epic field using the standard Increments names(Y23PI1, Y23PI2, Y23PI3, etc.)

So if a Team plans a Business Epic(BE-1) in Product Increment Y23PI1 then my PIE chart when I filter on Y23PI1 should show 1.

When this Business Epic isn't completed in Y23PI1, teams then add the next PI, Y23PI2 to the Fixed Version field on BE-1 to show that the Business Epic was in Y23PI1 and now is in Y23PI2.

So there is only one Business Epic being worked on. When I filter on Y23PI1 the PIE chart should not show me 2 slices ie one for "Y23PI1" and another for "Y23PI1, Y23PI2". It should only show me 1 when I filter on Y23PI1 and should show 1 when I filter on Y23PI2. The filter show never be a combination of the 2 PIs.

The Business Epic(BE-1) is the unique key that I pull through per PI.

So Portfolio Managers want to filter per PI and not a combination of PIs.

I hope this helps explain.
Note: I can share an example of my file with you but I have no idea how to upload a file to my reply.

Re: Filter for multiple values in one cell

KubenM — Wed, 08 Feb 2023 08:35:56 GMT

So all the visuals should show data per a defined list of PI dates:

Y23PI1

Y23PI2

Y23PI3

Y23PI4

Y23PI5

Each Bar visual should therefore only show these defined PI dates and not show for example a combination of PI dates like a column for "Y23PI1, Y23PI2" and another column showing 3 PI dates.

Based on my previous comment, we would like to see how many Business Epics have been planned per PI even if the same Business Epic rolled over from one PI to PI. So the key here is to show the single PI date and how many Business Epics were planned in that particular PI date.

Re: Filter for multiple values in one cell

tamerj1 — Wed, 08 Feb 2023 08:40:29 GMT

KubenM
I think a sample data with multiple BE's and PI's along with the expected result will better explain the problem. One example that produces a count of 1 drives to think what is the use of such pie chart that is just a circle with the number 1 😅

I know there is much more in that.

Re: Filter for multiple values in one cell

tamerj1 — Wed, 08 Feb 2023 08:43:47 GMT

KubenM
This is exactly what I though. Please provide a reasonable set of sample dummy data that simulates the situation in order to work with.

Re: Filter for multiple values in one cell

KubenM — Wed, 08 Feb 2023 09:10:12 GMT

I hope this illustration helps.

Re: Filter for multiple values in one cell

tamerj1 — Wed, 08 Feb 2023 12:09:39 GMT

KubenM
Sorry for the late response. I was trapped in a couple of meetings. I hope the following is what you're looking for.

Count of BE Key = CALCULATE ( COUNTROWS ( VALUES ( 'Table'[BE Key] ) ), FILTER ( 'Table', VAR SelectedValues = VALUES ( FilterTable[Item Value] ) VAR String = 'Table'[Fixed Version] VAR Items = SUBSTITUTE ( String, " , ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) RETURN COUNTROWS ( INTERSECT ( T2, SelectedValues ) ) ) )

Re: Filter for multiple values in one cell

KubenM — Thu, 09 Feb 2023 07:08:59 GMT

Dear tamerj1

Thank you for all your efforts in this regard. I am going to play around with the information you provided and will let you know if it works.
The last pie chart and bar chart on your reply is what I am looking for. Hopefully I can apply the DAX and make the visuals respond to the 5 unique filter dates.
Thank you