Unique Characters in string using DAX. Can we do it in a measure?

[tanujg]

I have a measure that concatenate month numbers. If Jan, Feb, March March March April April is selected it gives me back 1233344. I want to extract unique characters from it using DAX and preferably a measure. The result should be another 1234. Support from PBI gurus out here in this community will be highly appreciated.

[PhilipTreacy · Accepted solution]

Hi @tanujg 

Download my sample PBIX with the following code.

In the example string you've provided, how do you know that the first 2 characters don't indicate December?  How do you know that the 12 at the start of the string means Jan and Feb?

If you join the separate month numbers with a delimiter then that issue is solved so you end up with 1,2,3,3,3,4,4, (note the comma at the end of the string - that's important as I'll explain later).

However if you have selected some months and December, so your string looks like this 1,2,3,3,3,4,4,12, then the result you are looking for would be 123412.  How do you know that the last 2 digits mean Dec and not Jan, Feb again?

If you change your measure so that it concatenates the month numbers with a comma separator and it must always end the string with a comma, then this measure will give you the result you want

Unique String = 

VAR Jan = IF(CONTAINSSTRING([Measure], "1,"), "1", "")
VAR Feb = IF(CONTAINSSTRING([Measure], "2,"), "2", "")
VAR Mar = IF(CONTAINSSTRING([Measure], "3,"), "3", "")
VAR Apr = IF(CONTAINSSTRING([Measure], "4,"), "4", "")
VAR May = IF(CONTAINSSTRING([Measure], "5,"), "5", "")
VAR Jun = IF(CONTAINSSTRING([Measure], "6,"), "6", "")
VAR Jul = IF(CONTAINSSTRING([Measure], "7,"), "7", "")
VAR Aug = IF(CONTAINSSTRING([Measure], "8,"), "8", "")
VAR Sep = IF(CONTAINSSTRING([Measure], "9,"), "9", "")
VAR Oct = IF(CONTAINSSTRING([Measure], "10,"), "10", "")
VAR Nov = IF(CONTAINSSTRING([Measure], "11,"), "11", "")
VAR Dec = IF(CONTAINSSTRING([Measure], "12,"), "12", "")

RETURN Jan & Feb & Mar & Apr & May & Jun & Jul & Aug & Sep & Oct & Nov & Dec

NOTE: The string must end with a comma so that when the measure can distinguish between Jan, Feb, Oct, Nov and Dec, for example if it searched for 1 it needs to know if that 1 is Jan not the 1 in 10, 11 or 12.  So it searches for 1, instead which can only occur with Jan.

Regards

Phil

[Ashish_Mathur]

Hi,

How can March be selected multiple times in a slicer?  I am unable to visualise your actions/tasks.  Share some data and show your expected result.

[amitchandak]

@tanujg , I think this can be done easily in power query column then DAX measure

Text.Combine(List.Distinct(Text.ToList([column])),"")