Get count values for comma separated

Question

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.&nbsp;Table :&nbsp;Result expected :&nbsp;Regards,Ashfiya&nbsp;&nbsp;&nbsp;

greg_deckler · Answer

Anonymous&nbsp;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] &gt; -1))
RETURN
  __Result

freemanz · Answer

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.&nbsp;&nbsp;It is similar to the other two tables.&nbsp;

anonymous · Answer

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

freemanz · Answer

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.&nbsp;

anonymous · Answer

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!&nbsp;amitchandak&nbsp;Greg_Deckler&nbsp;Jihwan_Kim&nbsp; Please help

Forum Discussion

Related content

Counting nulls

Drill Through with comma separated values

Comma Separated Values

separate values into columns

Count of values not accurate