Forum Discussion
Switch and Counts are not working
Hi Team,
i am having the data as below.
| Error_Message | Value |
| Name | A |
| Name, Address | A |
| Address | A |
| Name,Address,Phone | A |
| Address,Phone | A |
| Phone | A |
| Name, Phone | A |
| Name,Address | A |
| Nmae | A |
And expecting the result as
| Error | Count |
| Name | 6 |
| Address | 5 |
| Phone | 4 |
I tried using Switch case which is not allowing me to count the duplicates which are got satisfied with other names.
ERROR = SWITCH(TRUE(),SEARCH("Phone",'TEST'[ERROR MESSAGE],,0) <> 0,"PHONE_ERROR",SEARCH("Address, Phone",'TEST'[ERROR MESSAGE],,0)<> 0,"ADDRESS_ERROR","NAME_ERROR")
COUNT = COUNT('TEST'[VALUE])
Please help me in getting the expected results
Thanks In Advance
Regards,
Udsan77
4 Replies
Hi Team,
Can someone help me in resolving the below issue?
Thanks in Advance
Regards,
Uday Yeluru
- Johanno
Continued Contributor
Since noone else has replied:
If you have a limited amount of error codes and a limited amount per row you could:
1. Use Power Query to separate to columns with comma seperator
2. Create one measure per error code:
Name = CALCULATE(COUNTROWS(Test);Test[Column1.1]="Name") + CALCULATE(COUNTROWS(Test);Test[Column1.2]="Name") + CALCULATE(COUNTROWS(Test);Test[Column1.3]="Name")
Phone = CALCULATE(COUNTROWS(Test);Test[Column1.1]="Phone") + CALCULATE(COUNTROWS(Test);Test[Column1.2]="Phone") + CALCULATE(COUNTROWS(Test);Test[Column1.3]="Phone")
etc.
But this is probably not a good looking solution.
Here was a similar question:
Thanks Johanno.
I am not able to achive the expected result with this solution.
I tried spliting the column based on delimiter comma by rows and use the Switich and Count its working.
Regards,
Udsan
- Ashish_Mathur
Super User
Hi,
Using the Query Editor, right click on the first column > Split column > By delimier. Specify the delimiter as , and click on Advanced > By rows > Finish/OK. Now right click on the column > Transform > Trim. Click on Close and Load. Now build your visual/create your measure.
