topic Re: How do I group (count) all the incident tickets with a multiple values in a column? in DAX Commands and Tips

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

Anonymous — Mon, 25 Nov 2024 14:55:53 GMT

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!

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

PhilipTreacy — Mon, 25 Nov 2024 23:28:55 GMT

Anonymous

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

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

Anonymous — Tue, 26 Nov 2024 15:23:13 GMT

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

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

Anonymous — Tue, 26 Nov 2024 16:31:19 GMT

This error doesn't make sense...

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

Anonymous — Tue, 26 Nov 2024 17:45:54 GMT

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.

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

Anonymous — Wed, 27 Nov 2024 01:05:09 GMT

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

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

Anonymous — Thu, 28 Nov 2024 07:14:11 GMT

Hi Anonymous , 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.

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

Anonymous — Fri, 29 Nov 2024 14:55:59 GMT

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?

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

Anonymous — Mon, 02 Dec 2024 02:42:45 GMT

Hi Anonymous ,

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

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

Anonymous — Mon, 02 Dec 2024 16:04:46 GMT

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?

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

Anonymous — Tue, 03 Dec 2024 01:47:25 GMT

Hi Anonymous ，

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.

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

Anonymous — Tue, 03 Dec 2024 14:44:39 GMT

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