Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Get count values for comma separated

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 : ...
DAX
Measures
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

 

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.