Counting multiple values within a single cell for different results

[Anonymous]

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 times, and if yes then I get a result, if not then go into another check.

Here is a similar example I could think of:

I want to check the title for VW, but give the output "VW" ONLY for TITLES that contain VW, not VW CV.

TITLES can contain both VW and VW CV, or none of both.

TITLE	WANTED RESULT
Test text VW and more text with VW CV	VW
Test text VW and nothing else	VW
Test text that only has VW CV	0
Test text with nothing here	0

My idea was to use a COUNTIF( function to see how many "VW" are in the title, if it is 2, then the WANTED RESULT should be VW as both will be in the TITLE.

If the COUNTIF( result is 1, then check to see if that title has "VW CV", if yes then the WANTED RESULT should be 0, otherwise 1 as it has to be "VW".

If the COUNTIF( result is 0, then the WANTED RESULT should be 0.

If someone could put this in DAX form that would be awesome!

Thanks!

[tamerj1 · Accepted solution]

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]

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]

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

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

[Anonymous]

You sir are a lifesaver!
Thank you so much