Filter for multiple values in one cell

[jbx]

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

[KubenM]

Hello @tamerj1 Even with changing the format of the column from text to whole numbers, I have no idea how to edit your original DAX to show the sum of Story Points Per each BE Size.
Please help. Thank you.

[tamerj1]

@KubenM 
Have you tried?

BE Size =
CALCULATE (
    SUM ( '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
            NOT ISEMPTY ( INTERSECT ( T2, SelectedValues ) ) && 'Table'[BE Size]
    )
) + 0

[KubenM]

Hello @tamerj1 I replaced SUM( ('Table'[BE Key]) ),with SUM( ('Table'[BE Size]) ), and the visual responds with exactly what I needed. Thank you soo much for assisting me in this regard. Bless You.

Sum of BE Size =

CALCULATE (

    SUM( ('Table'[BE Size]) ),

    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

            NOT ISEMPTY ( INTERSECT ( T2, SelectedValues ) ) && 'Table'[BE Size]

    )

) + 0

[tamerj1]

@KubenM 
Apologies. was just a typo mistake.

[KubenM]

Hello @tamerj1 Absolutely no reason for apologies. Through your support, I have learnt how to read some of the DAX and edit them to make them work 🙂 Once again thank you for your assistance provided. 

[KubenM]

Hello @tamerj1 I created the New Measure and dragged it onto a new page for Power BI to select a suitable visual and I get an error.  Please assist.

[KubenM]

Good Morning @tamerj1 It must be whole numbers. Apologies for the oversight.

[KubenM]

Dear @tamerj1 You are really been my inspiration and support over the past 2 weeks. Thank you.
Thank you for the DAX. 
Based on your previous DAX, this is what I used and it returns the same result. Only thing I didn't add was the +0 at the end of the DAX so that my card visual displays a 0 instead of the word blank 🙂

Count of BE Without Epic =

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

NOT ISEMPTY ( INTERSECT ( T2, SelectedValues ) ) && 'Table'[BE Without Epic] = ""

))

Once again that you for all your assistance. Much appreciated.
This DAX thing is truly powerful.

[KubenM]

Dear tamerj1

I applied the DAX to my Power Bi Report and the visuals work perfectly. Thank you for much for assisting in this regard. I can't thank you enough for your incredible support.
My colleague who understands a bit of DAX told me that the DAX you wrote does a unique count of the BE Key. May I ask which part of the DAX handles the unique count. I want to use the exact same DAX for other visuals but without the unique count. I would like to see Total Count. Thank you.

BE Key Filter =

CALCULATE (

    COUNTROWS ( VALUES ( 'PIP-Business Epics Committed (2)'[BE Key] ) ),

    FILTER (

        'PIP-Business Epics Committed (2)',

        VAR SelectedValues = VALUES ( Increment[Increment] )

        VAR String = 'PIP-Business Epics Committed (2)'[Increment]

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

    )

)

[tamerj1]

Hi @KubenM 

The distinct count is actually the COUNTROWS ( VSLUES ( 'Table'[Column] ) )

for you example the normal count would be

BE Key Filter =

    COUNTROWS (

    FILTER (

        'PIP-Business Epics Committed (2)',

        VAR SelectedValues = VALUES ( Increment[Increment] )

        VAR String = 'PIP-Business Epics Committed (2)'[Increment]

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

    )

[KubenM]

OMG 

You are super brilliant. Do you have a computer for a brain 🙂

Thank you once again for ALL your assistance in this regard. Really really appreciated.

[KubenM]

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

[KubenM]

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.

[tamerj1]

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

[KubenM]

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

[v-cgao-msft]

Hi @jbx ，

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

[tamerj1]

Hi @jbx 
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

[tamerj1]

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

[jbx]

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.