topic Re: Get count values for comma separated in DAX Commands and Tips

Get count values for comma separated

Anonymous — Fri, 18 Nov 2022 04:49:50 GMT

Hi,

I have a table below. Tenant ID will always be unique. I need a measure for Incident Type, Priority, and Incident Root cause to get the result below.

Table :

Result expected :

Regards,
Ashfiya

Re: Get count values for comma separated

FreemanZ — Fri, 18 Nov 2022 05:20:53 GMT

This shall be achieveable without any DAX code:

For the incidents by type table, create a table visual, put the [Incident Type] and [TelantID] to the Columns. Then you will get the expected result.

It is similar to the other two tables.

Re: Get count values for comma separated

Anonymous — Fri, 18 Nov 2022 05:49:08 GMT

Hi FreemanZ ,

This will not work. I need count for every value present in Incident Type
Ex: Near miss is present for all three tenant ID, so count of near miss should be 3.

Re: Get count values for comma separated

FreemanZ — Fri, 18 Nov 2022 06:29:50 GMT

Aha, then you need to split [Incident Type] column and duplicate the rest columns first in Power Query. For example, a row with 3 incident types, there will be 3 rows.

Re: Get count values for comma separated

Anonymous — Fri, 18 Nov 2022 07:03:08 GMT

I do not want to go with that approach since I will have 40 more comma-separated columns with different values.

So I need a calculated measure.
Thank You!

amitchandak Greg_Deckler Jihwan_Kim Please help

Re: Get count values for comma separated

Greg_Deckler — Fri, 18 Nov 2022 14:46:24 GMT

Anonymous Assuming you have an Incidents type table somewhere you could do this:

Measure = VAR __Type = MAX('Types'[Type]) VAR __Table = ADDCOLUMNS( 'Table', "__Found",SEARCH(__Type,[Incident Type],,-1) ) VAR __Result = COUNTROWS(FILTER(__Table, [__Found] > -1)) RETURN __Result

Re: Get count values for comma separated

Anonymous — Tue, 22 Nov 2022 09:05:15 GMT

Hi Greg_Deckler
Thanks for the reply. I will not have an Incident Type table. My issue is there are more than 40 such columns, so having 40 tables will lower the performance.
Any different approach?

Note : using MongoDb as Datasource which is very very slow.