topic Re: Counting multiple values within a single cell for different results in DAX Commands and Tips

Counting multiple values within a single cell for different results

Anonymous — Sat, 26 Nov 2022 10:25:28 GMT

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!

Re: Counting multiple values within a single cell for different results

tamerj1 — Sat, 26 Nov 2022 20:46:41 GMT

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

Re: Counting multiple values within a single cell for different results

Anonymous — Mon, 28 Nov 2022 08:01:36 GMT

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".

Re: Counting multiple values within a single cell for different results

tamerj1 — Mon, 28 Nov 2022 08:14:01 GMT

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

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

Re: Counting multiple values within a single cell for different results

Anonymous — Mon, 28 Nov 2022 08:18:05 GMT

You sir are a lifesaver!
Thank you so much