Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Counting multiple values within a single cell for different results

I have a solution to a problem that I have but am not able to write it in DAX, in Excel this would mainly include COUNTIF(. I need to check a cell content to see if it contains a specific word 2 tim...
count distinct with condition
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    Please refer to attached sample file with the solution

    RESULT = 
VAR String = 'Table'[TITLE]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR Check1 = COUNTROWS ( FILTER ( T2, [@Item] = "VW" ) )
VAR Check2 = COUNTROWS ( FILTER ( T2, [@Item] = "CV" ) )
VAR Result =
    SWITCH ( 
        TRUE ( ),
        Check1 = 0, "0",
        Check1 > 1, "VW",
        Check2 >= 1, "0",
        "VW"
    )
RETURN
    Result
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Anonymous 
Please refer to attached sample file with the solution

RESULT = 
VAR String = 'Table'[TITLE]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR Check1 = COUNTROWS ( FILTER ( T2, [@Item] = "VW" ) )
VAR Check2 = COUNTROWS ( FILTER ( T2, [@Item] = "CV" ) )
VAR Result =
    SWITCH ( 
        TRUE ( ),
        Check1 = 0, "0",
        Check1 > 1, "VW",
        Check2 >= 1, "0",
        "VW"
    )
RETURN
    Result
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi tamerj1 

Thank you for your suggestion, it seems to work mostly. Can I add more filter expressions to this? So that I can filter tickets not only by "CV" but also additional expressions like "VW N".

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 
    If I correctly understand, then you may change the valriable "Check2"

    VAR Check2 = COUNTROWS ( FILTER ( T2, [@Item] IN { "CV", "N" } ) )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      You sir are a lifesaver!
      Thank you so much