Forum Discussion
Separated Texts in Matrix
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 |
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
4 Replies
rajulshahResident Rockstar
Hello Anonymous ,
Can you please post sample data with the expected result? I am still not able to figure out the question.
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 🙂
- v-luwang-msft
Community Support
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