Trying to create a Measure by counting distinct rows of a table variable

[Joao_Marto]

Hi Community,
I ask your help in something that seems pretty simple, but I´m unable to find an answer. 
I want to calculate, in a card visualization, the nº of different values that exist on a particular column in my dataset.
The particularity of this column ("Column1") is that accepts multiple values, separated by a comma. For example, consider the simple table below.

In this particular case, the result should be 11. i want to do it without having to create a new table or modify my current one.
I´m itrying to combine the following steps, without success:
1º create a table variable with only 1 column and contains only the different values of Column1 (Addcolumns+Summarize)
2º in the new table, add new rows for each different value on each row (Generateseries?)
3º count the distinct number of rows

Im very confused right now and I dont know how to use the column of my new table variable inthe distinctcount
Please help
thank you in advance

54
216,549
992,97,769,1125,1127
823
96
823
93

[Sahir_Maharaj]

Hello @Joao_Marto,

Can you please try this DAX:

Distinct Value Count = 
VAR Separator = ","
VAR ExpandedTable = 
    GENERATE(
        ALL('YourTableName'), 
        VAR CurrentValues = [Column1]
        VAR ValueList = UNICHAR(10) & SUBSTITUTE(CurrentValues, Separator, UNICHAR(10)) & UNICHAR(10)
        RETURN
            GENERATESERIES(1, LEN(ValueList) - LEN(SUBSTITUTE(ValueList, UNICHAR(10), "")), 1)
    )
VAR ResultTable = 
    ADDCOLUMNS(
        ExpandedTable,
        "Value", 
        TRIM(MID(SUBSTITUTE([Column1], Separator, REPT(" ", 100)), ([Value] - 1) * 100 + 1, 100))
    )
RETURN
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                ResultTable,
                "TrimmedValue", [Value]
            )
        )
    )

[Joao_Marto · Accepted solution]

Hi Sahir,
thank you for your help!
It didnt quite return the correct values in my original dataset.  Is there any limitation in the number of characters a row can have? because the returned value is lower than the correct one which make me think that probably is not able to cover all characters.

It also returned a minor error: "Function "addcolumns" cannot add column [Value] since it already exists".
I made a small change that was accepted:

VAR ResulTable =

    ADDCOLUMNS(

        ExpandedTable,

        "TimmedValue",

        TRIM(MID(SUBSTITUTE('Atividades DPR'[Empresa(s) Adjudicada(s)_id],separator,REPT(" ", 100)),([Value]-1)*100+1,100)))

Return

    Countrows(

        DISTINCT(

            SELECTCOLUMNS(

                ResulTable,

                "TrimmedValue",[Value])))