Switch and Counts are not working

[Udsan77]

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

[Ashish_Mathur]

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.

[Udsan77]

Hi Team,

Can someone help me in resolving the below issue?

Thanks in Advance

Regards,

Uday Yeluru

[Johanno]

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:

https://community.powerbi.com/t5/Desktop/String-Text-search-for-keywords-in-multiple-columns/td-p/41...

[Udsan77]

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