Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Compare two strings alphabetically

Hello,

I have a list of activities id and subactivities id.

I want to create an index column for each activity so the table would look like this:

Activity id	Sub-activity id	Index
Act-1		0
Act-1	Sub-act 1	1
Act-1	Sub-act 2	2
Act-1	Sub-act 3	3
Act-2		0
Act-2	Sub-act 4	1
Act-2	Sub-act 5	2
Act-3		0
Act-3	Sub-act 6	1

I thought that I could just do:

Index =

var act_id = [Activity id]

var subact_id = [Sub-activity id]

return

IF(

ISBLANK([Sub-activity id]),

0,

CALCULATE(

COUNT('my_table'[Sub-activity id]),

FILTER(

'my_table',

'my_table'[Activity id] = act_id

&& 'my_table'[Sub-activity id] < subact_id

)

)

)

The idea was to compare the strings that I have for the sub-activity id alphabetically but using the "<" does not work.

Does anyone know how to do that without using the "<" ?

FarhanAhmed
4 years ago
Does string comes with consistent value and Numbers at last ?

You may want to split this column thru Power Query to sub activity and its number and then use the number column in the DAX expression.

3 Replies

FarhanAhmed
Community Champion
4 years ago
Does string comes with consistent value and Numbers at last ?

You may want to split this column thru Power Query to sub activity and its number and then use the number column in the DAX expression.
- Anonymous
  Not applicable
  4 years ago
  Yes they all begin with the three same letters and then it's all numbers.
  Thank you for the answer ! I can see that this will work.
NickolajJessen
Solution Sage
4 years ago
Here's an alternative soloution, should you ever need it.
Step 1: Make an index coloum in power Query that sorts in alphabetically (This could require a concatenation on Activity and activityid, if your data isn't already entered in the correct order)
Step 2:

Related content