topic Re: splitting string by delimiter and comparing the results for mismatch values in DAX Commands and Tips

splitting string by delimiter and comparing the results for mismatch values

Msri — Thu, 14 Nov 2024 01:37:53 GMT

Hi All,

I have a dataset containing two string columns(Region names). The values are combined with a delimiter ;

I need a distinct count of rows that have a mismatch between RegionA and RegionB for a date.

I will need to use the measure in a table to show the count of mismatch rows (values not matching or when there is null) when Agency = "Yes".

From the below example, For 11/1/2024, the total should be 2(345 is not counted as Agency = No),

for 11/8/2024 the total will be 1

Thanks in advance for your time and help.

Date	ColumnA	Agency	RegionA	RegionB	Mismatch
11/1/2024	123	Yes	Phoenix;Charlotte	Phoenix	1
11/8/2024	123	Yes	Phoenix;Charlotte	Phoenix;Charlotte	0
11/1/2024	345	No	Edison;Atlanta		1
11/1/2024	567	Yes		Tampa;Raleigh	1
11/8/2024	345	Yes	Edison;Atlanta	Edison	1
11/8/2024	567	Yes	Tampa;Raleigh	Tampa;Raleigh	0

Re: splitting string by delimiter and comparing the results for mismatch values

FreemanZ — Thu, 14 Nov 2024 02:38:56 GMT

hi Msri ,

try to plot a visual with date column and a measure like:

measure = COUNTROWS( CALCULATETABLE( data, data[Agency]="Yes", data[RegionA]<>data[RegionB] ) )

it worked like:

Re: splitting string by delimiter and comparing the results for mismatch values

shafiz_p — Thu, 14 Nov 2024 03:26:18 GMT

Hi Msri Try below code:

MismatchCountByDate_V2 = CALCULATE( COUNTROWS( FILTER( 'Table', [Agency] = "Yes" && ( ISBLANK([RegionA]) || ISBLANK([RegionB]) || [RegionA] <> [RegionB] ) ) ), ALLEXCEPT('Table', 'Table'[Date]) )

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

Re: splitting string by delimiter and comparing the results for mismatch values

tamerj1 — Thu, 14 Nov 2024 05:18:53 GMT

FreemanZ

I guess the problem "which is not described clearly" is when the regions order is different between Region A and Region B columns yet they're still need to be considered as match.

Re: splitting string by delimiter and comparing the results for mismatch values

FreemanZ — Thu, 14 Nov 2024 05:47:12 GMT

hi tamerj1 ,

Highly probably yes, as that looks more like reality.

If that is the case, DAX code will need to split, sort, and then combine the regions, before any comparison. Or first process it in Power Query.

How would you solve issues like this?

Re: splitting string by delimiter and comparing the results for mismatch values

tamerj1 — Thu, 14 Nov 2024 16:18:25 GMT

FreemanZ

Exactly

Re: splitting string by delimiter and comparing the results for mismatch values

Msri — Thu, 14 Nov 2024 17:50:59 GMT

Yes, in SQL i used string_split which didnt need to sort the dataset, but that is what needed to be done in DAX. Both the Region columns have grouped rows. Will it be better to have seperate DAX to split them and then use it to do comparision on both columns. Can you help me with splitting out the columns as well

Re: splitting string by delimiter and comparing the results for mismatch values

Msri — Thu, 14 Nov 2024 18:13:53 GMT

Thanks Shahariar, it is giving me the same count for every zone.

Re: splitting string by delimiter and comparing the results for mismatch values

tamerj1 — Thu, 14 Nov 2024 18:40:07 GMT

Msri

This calculation can be very expensive in terms of performance. Therefore, I would recommend to have it as a calculated column. The measure can be a simple sum of the column.

Mismatched =
VAR StringA = 'Table'[RegionA]
VAR ItemsA =
SUBSTITUTE ( StringA, ";", "|" )
VAR LengthA =
COALESCE ( PATHLENGTH ( ItemsA ), 1 )
VAR T1A =
GENERATESERIES ( 1, LengthA, 1 )
VAR T2A =
SELECTCOLUMNS ( T1A, "@ItemA", PATHITEM ( ItemsA, [Value] ) )
VAR StringB = 'Table'[RegionB]
VAR ItemsB =
SUBSTITUTE ( StringB, ";", "|" )
VAR LengthB =
COALESCE ( PATHLENGTH ( ItemsB ), 1 )
VAR T1B =
GENERATESERIES ( 1, LengthB, 1 )
VAR T2B =
SELECTCOLUMNS ( T1B, "@ItemB", PATHITEM ( ItemsA, [Value] ) )
VAR Result =
INT ( NOT ISEMPTY ( EXCEPT ( INTERSECT ( T2A, T2B ), T2A ) ) )
&& 'Table'[Agency] = "Yes"
RETURN
Result

Re: splitting string by delimiter and comparing the results for mismatch values

Msri — Thu, 14 Nov 2024 19:10:57 GMT

Thank you tamerj1 , Will it be better if i split both the columns in power query and then use DAX to do a comparision on the columns for every ID column and date?

Please let me know the DAX that is needed for that situation

Date	ColumnA	Agency	RegionA	RegionB
11/1/2024	123	Yes	Phoenix	Phoenix
11/1/2024	123	Yes	Charlotte
11/8/2024	123	Yes	Phoenix	Phoenix
11/8/2024	123	Yes	Charlotte	Charlotte
11/1/2024	345	No	Edison
11/1/2024	345	No	Atlanta
11/1/2024	567	Yes		Tampa
11/1/2024	567	Yes		Raleigh
11/1/2024	567	Yes		Memphis
11/8/2024	345	Yes	Atlanta	Edison
11/8/2024	345	Yes	Edison
11/8/2024	567	Yes	Tampa	Tampa
11/8/2024	567	Yes	Raleigh	Raleigh

Re: splitting string by delimiter and comparing the results for mismatch values

Msri — Fri, 15 Nov 2024 04:47:17 GMT

i split both the columns in power query and then use DAX to do a comparision on the columns for every ID column and date?

Please let me know the DAX that is needed for that situation

Date	ColumnA	Agency	RegionA	RegionB
11/1/2024	123	Yes	Phoenix	Phoenix
11/1/2024	123	Yes	Charlotte
11/8/2024	123	Yes	Phoenix	Phoenix
11/8/2024	123	Yes	Charlotte	Charlotte
11/1/2024	345	No	Edison
11/1/2024	345	No	Atlanta
11/1/2024	567	Yes		Tampa
11/1/2024	567	Yes		Raleigh
11/1/2024	567	Yes		Memphis
11/8/2024	345	Yes	Atlanta	Edison
11/8/2024	345	Yes	Edison
11/8/2024	567	Yes	Tampa	Tampa
11/8/2024	567	Yes	Raleigh	Raleigh

Re: splitting string by delimiter and comparing the results for mismatch values

Anonymous — Tue, 19 Nov 2024 07:38:30 GMT

Hi Msri ,

You can try the following expression.

Column = var _regiona= CONCATENATEX( FILTER(ALL('Table'), [Date]=EARLIER([Date])&&[ColumnA]=EARLIER([ColumnA])&&[Agency]="Yes"), [RegionA],"-") var _regionb= CONCATENATEX( FILTER(ALL('Table'), [Date]=EARLIER([Date])&&[ColumnA]=EARLIER([ColumnA])&&[Agency]="Yes"), [RegionB],"-") var _ifb= IF( CONTAINSSTRING( _regiona,[RegionB])=TRUE()&&[RegionB]<>BLANK(),1,0) var _ifa= IF( CONTAINSSTRING( _regionb,[RegionA])=TRUE()&&[RegionA]<>BLANK(),1,0) return IF( _ifa=1&&_ifb=1,1,0)

Column 2 = CALCULATE(DISTINCTCOUNT('Table'[ColumnA]),FILTER(ALL('Table'),[Date]=EARLIER([Date])&&[Column]=1))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.