cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Riconic
New Member

Concatenating values and comparing them regardless of the order

Hello 

 

I have a column that concantenates a route: {Brussells, Paris, Madrid}.

 

I'd like to make a distinct count that would consider {Brussells, Paris, Madrid} = {Paris, Brussells, Madrid} as ONE route as I'd like to count the number of routes, regardless of the order.

 

Thanks for your help !!

1 ACCEPTED SOLUTION

Found it, it was merely to change the 'ORDER_By expression' argument in the concatenatex to have it sorted in alphabetical order instead of stop number.

Then the routes gets the same unique sequence string as they are sorted in alphabetical order.

 

 

View solution in original post

3 REPLIES 3
Tanushree_Kapse
Continued Contributor
Continued Contributor

Hi @Riconic ,
Trying creating a measure:
Countvalues= Calculate(COUNT(Route),Filter(table_name, Route="Brussells, Paris, Madrid" || Route="Brussells, Madrid, Paris" || Route="Madrid, Brussells, Paris" ||  Route=" Paris, Brussells, Madrid" || Route="  Paris, Madrid, Brussells" || Route="Madrid, Paris, Brussells" ))

I hope this helps!

Hi Tanushree_Kapse 

thanks for your answer.

 

Actually I should have been more specific.

The column "route" is a concatenateX of another column, the location address. 

The route is a sequence of different locations (from 2 to 5) with a separator.

The order does not matter in the sequence count, so A_B_C / B_A_C / C_A_B / C_B_A should be considered as the same route.

Since I have hundreds of possible routes, I can not hard code all the route sequences in the measure.

 

Thanks again !

Found it, it was merely to change the 'ORDER_By expression' argument in the concatenatex to have it sorted in alphabetical order instead of stop number.

Then the routes gets the same unique sequence string as they are sorted in alphabetical order.

 

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.