Create a Measure by counting distinct rows of a table variable

Question

I ask for 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 ("Supplier") is that accepts multiple values, separated by a comma. For example, consider the simple table at the bottom of the post.

In this particular case, the result should be 11. i want to do it without having to create a new table in my model or modify my current dataset.

I´m trying to combine the following steps, without success: 1º create a table variable with only 1 column that gathers the different values of Supplier. Should be:

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

2º in the new table, add new rows for each different value on each row (Generateseries?) Should be:

Variable Table_Column1
54
216
549
992
97
769
1125
1127
823
96
93

3º count the distinct number of rows Distinctcount (Variable Table_Column1) = 11

Im very confused right now and I dont know how to use the column of my new table variable in the distinctcount.

Please help thank you in advance

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

Answer 1

Best to do this in Power Query:

In Power Query, split your column by , into new Rows...

(sorry that the image is showing / for the separator, it should be comma.)

Then you'll have it.

---

If you really need a DAX measure, then try this:

Supplier count = 
  var tblPath =
    ADDCOLUMNS(
      ADDCOLUMNS(
        DISTINCT('YourTable'[Supplier]),
        "iPath", SUBSTITUTE( 'YourTable'[Supplier], ",", "|")
      ),
      "iPathLength", PATHLENGTH([iPath])
    )
  var tblPathCum = 
    ADDCOLUMNS(
      tblPath,
      "cumLength", SUMX( FILTER(tblPath, [iPath] <= EARLIER([iPath])), [iPathLength])
    )
  var tblGen = 
    SELECTCOLUMNS(
      ADDCOLUMNS(
        GENERATESERIES(1, SUMX(tblPath, COALESCE([iPathLength], 0) ) ),
        "cum", MINX( FILTER(tblPathCum, [cumLength] >= [Value]), [cumLength] )
      ),
      "items", MAXX( FILTER(tblPathCum, [cumLength] = [cum]), [iPath] ),
      "itemNum", [cum] - [Value] + 1
    )
  var tblItem = 
    SELECTCOLUMNS(
      tblGen,
      "item", PATHITEM( [items], [itemNum])
    )

  return COUNTROWS( DISTINCT(tblItem) )

The key behind this is leveraging the DAX Parent and Child functions, PATHLENGTH and PATHITEM. These work with a pipe delimited string, hence the SUBSTITUTE from , to |.