Calculating text totals across multiple columns - need help!

[warnera]

I have a rather large report that pulls top five medications in a seperate column. The total number of possible medications is very long. What I would like to do is count (and display) the total number of times the medicaiton appears across all 5 columns. I am not sure that I can unpivot the data because they are linked to patient ID numbers and a bunch of other metrics.

                  Med 1     Med 2    Med 3    Med 4   Med 5

Patient 1       c             a        

Patient 1       b              c             d

Patient 2       a              b              c            d             e

Patient 2       c              d             b            d             e     

Patient 3        b             c              d      

Would like to be able to write a measure (or something) to be able to simply show the following (keep in mind there are a lot more medications than what I put in my example using letters).

a = 2 

b =4

c = 5

d = 5

e = e

Thanks in advance!

[v-kelly-msft]

Hi @warnera ，

Go to "edit queries">"Transform">"Unpivot Columns":

Replace the blank string will "null":

Then create a measure as below:

Measure = COUNT('Table'[Value])

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

[warnera]

Kelly, thank you for the prompt response. I tried doing this p/t asking and kept getting the following error...so I assumed it was not right. Any suggestions? Thanks again...as you can tell I am self taught.

error

[warnera]

Disregard. I realized that I had a conditional column that had blank fields. I replaced values and added nullAAA just to fill the void and it worked. Thanks again!