Need a DAX function to get unique count of values separated by "comma" excluding Blanks

[User232431]

Hi I have table containd column as Values (which has comma seperated values) , how can i get unique count of values based on slicer selection,

ex: Year = 2025 , Month = 1 , Country = ind , the result will be  "4" (unique values  - 20,30,40,13)

ex2 : Year = 2025 , Month = 1 , Country = ind ,Market = MH then the result will be "3" (unique values - 20,30,40)

ex3 : Year = 2025 , Month = 1 , Country = ind ,Market = MH then the result will be "3" (unique values - 20,30,40)

ex4 : Year = 2025 , Month = 1 , Country = Mal ,Market = KU , Branch = B1 then the result will be "0" (Blanks)

Year	month	country	market	branch	values
2025	1	Ind	MH	MUM	20,30,40,20,30
2025	1	Ind	MH	MUM
2025	1	Ind	MH	Nag	30,40
2025	1	Ind	KH	Kan	13,30,40,20,30
2025	1	Mal	KU	B1

I have tried with below Dax , but not achieving desired output

_Unique_Concatenation_Count =

VAR UniqueValues =

    DISTINCT(

        VALUES(t2_Main[Den2])

    )

RETURN

COUNTROWS(UniqueValues)

Need help on to accurate output. Thanks

[lbendlin]

Note that you don't have blanks in the second row. You have a string " " which counts as a distinct value.

[User232431]

Thanks you for your reply ,  Can we convert it into dynaic measure ? so that it can calculate according to the slicer that i selected. Thanks you very much

[lbendlin]

You mean you want to calculate this across rows rather than for each row separately?

[User232431]

Yes , i want the measure that will be used in Card visual. Thank you

[lbendlin · Accepted solution]

Unique Measure = 
var a = SUBSTITUTE(CONCATENATEX('Table',[values],"|"),",","|")
RETURN IF(PATHLENGTH(a)=0,0,
VAR b = GENERATE(GENERATESERIES(1, PATHLENGTH(a)), SELECTCOLUMNS ({PATHITEM(a, [Value])}, "Val", [Value]))
RETURN COUNTROWS(SUMMARIZE(b, [Val])))

[User232431]

Yes , Thank you