Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !!
Solved! Go to 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.
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |