How do I group (count) all the incident tickets with a multiple values in a column?

[Hopeisgood1]

I have quite a bit of tickets that have multiple values in a column that are not in any order. Yet, I can't group these as 'Multiple HPs Selected'. 

You can see in the snapshot that the tickets with single select of HP are counted just fine but the ones in the smaller data numbers have the multiple values, I need these grouped/Counted together regardless the order of the HPs selected.

Column is 'HP Selected' and I just want the multiple values as 'Multiple HPs Selected' and group/counted from there. 

Thank you!

[PhilipTreacy]

@Hopeisgood1 

Create a new column which allows you to give each of the 'multiple values' a name that you can group them on whilst leaving the other values unchanged  e.g.

Column = 

SWITCH(

    TRUE(),

    [HP Selected] = "Alpha", "Alpha",

    [HP Selected] = "Beta", "Beta",

    [HP Selected] = "Gamma", "Gamma",

    "Multiple HP's Selected"

)

Which gives this

You can then create this visual using the new column

If that isn't what you want please supply some sample data.

Regards

Phil

[Hopeisgood1]

Hi @PhilipTreacy 

I was thinking this:

Would the coding be feasible where:

If no HP was selected, "No HP selected"

If 1 HP selected, USE that value as whatever the HP is

If 2 to 41 HPs selected, "Multiple HPs Selected"

If 42 or over, "All HPs Selected"

This HPs Selected is in a table now...

I am still learning the codings and this isn't my level of expertise yet. I am learning for you all here.

Thank you,

Holly

[Anonymous]

Hi @Hopeisgood1 , hello PhilipTreacy, thank you for your prompt reply!

Please check the following measures:

HP Selection Status = 
VAR HP_Count = 
    IF(
        SELECTEDVALUE('Table'[HP Selected])=BLANK(), 
        0,  
        LEN(SELECTEDVALUE('Table'[HP Selected])) - LEN(SUBSTITUTE(SELECTEDVALUE('Table'[HP Selected]), ",", "")) + 1  
    )
RETURN

    IF(
        HP_Count = 0, 
        "No HP Selected",  
        IF(
            HP_Count = 1, 
            SELECTEDVALUE('Table'[HP Selected]),  
            IF(
                HP_Count <= 41, 
                "Multiple HPs Selected",  
                "All HPs Selected"  
            )
        )
    )

 Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hopeisgood1]

Hi @Anonymous 

Thank you for your reply!!!

I know the company I work for is using an older version of Power BI Desktop and I am not able to make this work for some reason. The company restricted the upgrade due to HIPAA compliance.  I notice in your current version, you have what is called Measure Tools. I don't 😕 I am not sure if this is causing an issue for my Power BI version? 

[Anonymous]

Hi @Hopeisgood1 , 

Please note that I created the measures instead of the column, please check it:

More information about difference between calculated column and measure for your reference:

Measures vs Calculated Columns in DAX and Power BI | endjin - Azure Data Analytics Consultancy UK

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Hopeisgood1]

@Anonymous 

Ahhh! Ok, I fixed it and I can now see it in a Table in report view. Thank you for that education! My brain is still processing the link you provided. I am still learning this stuff.

Thou, I am not able to pull the data into the Y-axis of a stacked bar chart. Automatically went to tooltips box. Not sure why that is? Did I miss something from the link to why this wouldn't work?

[Anonymous · Accepted solution]

Hi @Hopeisgood1 ，

Measures in DAX are context-dependent calculations, meaning their values are determined by the context in which they are used.

Because of this, measures do not have fixed values and cannot be directly used for grouping or stacking in visualizations like stacked bar charts.

In this situation, we need to use calculated column to ensure the values are fixed and appropriate for the visualization.

HP Selection Status Column = 
VAR HP_Count = 
    IF(
        'Table'[HP Selected]=BLANK(), 
        0,  
        LEN('Table'[HP Selected]) - LEN(SUBSTITUTE('Table'[HP Selected], ",", "")) + 1  
    )
RETURN
    IF(
        HP_Count = 0, 
        "No HP Selected",  
        IF(
            HP_Count = 1, 
            'Table'[HP Selected],  
            IF(
                HP_Count <= 41, 
                "Multiple HPs Selected",  
                "All HPs Selected"  
            )
        )
    )

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hopeisgood1]

@Anonymous 

This is excellent! It works now and this is a great learning tool from you! Also from @PhilipTreacy on the switch in which I used for the Region column as data so simple in that one. 

Thank you Thank you Thank YOU!!!!

Holly

[Hopeisgood1]

HI Phil,

Reading upon SWITCH, it sounds like it's a bitty restrictive like not allowing those the same HP with the other HP values in some rows.  

But this is what I am looking for. Hope this makes sense?

And for some odd reason, Power BI is not recognizing the HP Selected in the SWITCH.

[Hopeisgood1]

This error doesn't make sense...

[Hopeisgood1]

Definitely checking that today - will keep you posted. Thank you!