Count unique values, but some cells have two or more values separated by comma

[Daniel_F]

Guys,

i have annoying task to do every month and thought maybe there is some sollution. On a screen below you will see different values that should be counted. I need to count how many times the same value is repeated. It seems nothing complicated, but in some cases there are several values ​​in one field separated by commas, f.e. in a 3rd row we have 3 values 8.15, 6.56 and 3.18, which must be counted as separate values. Value 3.18 repeats 3 times in total, so final result should be 3.18 - 3 

 

Below is final, expected result:

Any suggestion how to count each comma separated value as a separate value? 😉

PS.: the word str. will be removed via column transformation

[Daniel_F]

Thx for all 🙂 this is what i was looking for 🙂

[Mahesh0016]

Count Text = CALCULATE(COUNTX('Table',COUNT('Table'[_Str])),ALLSELECTED('Table'[Str]))

@Daniel_F IT IS HELPFUL FOR YOU

[TomMartens]

Hey @Daniel_F ,

in a situation like this, I create a new table that contains two columns STR and Row Labels. STR Still contains the comma separated values, whereas Row Labels has the string separated into multiple rows, then you can create a relationship with the original table and write a measure that counts the number of occurrences of a given string now, a Row Label.

The table will look like this:

In PowerQuery you start with a table containing a single column of unique values of STR, create a duplicate column of STR.
Then you can use the "Split column - By delimiter transformation on the duplicated column:

Without any measure you can achieve something like this:

Hopefully, this provides what you are looking for.

Regards,

Tom

[ddpl · Accepted solution]

@Daniel_F 
Step1: First Seperate the value by comma seperated as per below

result was:

Step2:Select all 3 column and unpivot selected column as per below

then remove unnecessary column and you can replace str. with blank 

now create visual table as per below

[Daniel_F]

Yes, this is what I need, but now another question has appeared. After i split my column and before unpivot i have to select all newly appeared columns and then select Unpivot selected columns, but what if i'll have more than 3 values ​​separated by comma, f.e. 4-5-6? Then applied steps skip those new 4-5-6 columns. So my question is - is there any function that dynamically unpivot all new appeared columns after split? Hope you understand what I mean 🙂

In Applied steps i see this function:

Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value")

[ddpl]

@Daniel_F you can add index column and then unpivot the other column.