Separated Texts in Matrix

[Anonymous]

Hi Community!

Hope all are safe an sound.

I have encountered an problem such that I cannot get a single count from the rows : Split Feature, Replace with delimiter methods  but still the matrix wont show correct numbers. I am trying to get a count for each parameter in cells.

I need a count for each of the parameters (1.10 ,1.11) in a single matrix with the parameters in rows

Kindly Assist 🙂

Regards,

Elston

1_Key Data Point

1.4 Hello, 1.10 Bye

1.16 No

1.4 Hello, 1.10 Bye

1.25 OK

1.10 Bye

1.7 David, 1.15 Chango

1.2 BVC

1.12 LALA

1.12 RIRI

1.10 Bye

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Try like following:

1.use the following dax create column:

kwth = SEARCH(",",'Table'[1_Key Names],,0)

k11 = IF('Table'[kwth]>0,LEFT('Table'[1_Key Names],'Table'[kwth]-1),'Table'[1_Key Names])

k12 = IF('Table'[kwth]>0,RIGHT('Table'[1_Key Names],LEN('Table'[1_Key Names])-'Table'[kwth]),BLANK())

k2wth = SEARCH(",",'Table'[2_Key Names],,0)

k21 = IF('Table'[k2wth]>0,LEFT('Table'[2_Key Names],'Table'[kwth]-1),'Table'[2_Key Names])

k22 = IF('Table'[k2wth]>0,RIGHT('Table'[2_Key Names],LEN('Table'[2_Key Names])-'Table'[k2wth]),BLANK())

Then get the below:

2.calculate table base on the new colunm,then merge :

Table 6 = (UNION('Table 3','Table 4','Table 5','Table 2'))

test = TRIM('Table 6'[k12])

Final use the follwoing measure:

count = COUNT('Table 6'[test])

Wish it is helpul for you!

You could download my pbix file if you need!

Best Regards

Lucien

[Anonymous]

Hi Community! 

No response on this one yet 😞

[rajulshah]

Hello @Anonymous ,

Can you please post sample data with the expected result? I am still not able to figure out the question.

[Anonymous]

Hi @rajulshah  - Thank you for your kind reply

Please find file Link:

https://drive.google.com/file/d/1kZAZkd2_q25Bc_YAw6UWjiTIfplhcGX4/view?usp=sharing 

On the left is how the data dump is received, I need to bifurcate it and get the count for each of the names in Power Bi matrix visual.

Hope this helps 🙂