Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
Hi Community!
No response on this one yet 😞
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 🙂