CODEWITHSUNDEEP
dax
Kshitij Manvelikar
Kshitij Manvelikar

Reputation: 73

Extract Unique Values from Single cell

Want to pull Distinct Values from Cell, Data looks like below. I need output as shown in Distinct Values Column in Power BI. Need DAX

enter image description here

Upvotes: 0

Views: 652

Answers (2)

Joe G
Joe G

Reputation: 1776

I got this idea from a post on the Power BI community site, and adapted it to your specific situation.

The general idea is to...

  1. Figure out how many letters are in the string.
  2. Create a list using GENERATESERIES and use the [Value] of that series to pull out letters from the original string.
  3. Deduplicate the list of letters.
  4. Count the rows.

The end result is a formula that looks like this.

Letter Count = 
    VAR NameLength = LEN([Names])
    VAR TempList = ADDCOLUMNS(
            GENERATESERIES(1, NameLength, 1),
            "Letter", MID([Names], [Value], 1)
        )
    VAR DeDupeList = SUMMARIZE(TempList, [Letter])

RETURN
    COUNTROWS(DeDupeList)

And here is the result.

enter image description here

Upvotes: 1

mxix
mxix

Reputation: 3659

Well this is awfull and maybe someone can do it in a better way but... I would not do this is DAX.

Mainly because I didn't find a way to handle the string size dynamically. So this is actually hardcoded for a maximum string length of 5

Distinct Letters = 
    VAR v = SELECTEDVALUE('Table'[Names])
RETURN
IF( HASONEVALUE('Table'[Names]);
   COUNTROWS(
        SUMMARIZE(
            FILTER({(MID(v;1;1));(MID(v;2;1));(MID(v;3;1));(MID(v;4;1));(MID(v;5;1))};[Value] <> BLANK()
            );
            [Value]
        )
    )
;
BLANK()
)

Upvotes: 0

Related Questions