Count text values from different columns

Question

I have data like below

OrderID	Category1	Category2	Category3
11	A	B	A
12	A	B	C
13	A	A	C

I need to count across all 3 columns how many A's, B's and C's are there?

Point to consider here is for order 11 Cat1 and Cat3 have "A" twice. Should count it as 1 instead of 2. Same way for order 13 Cat1 and Cat2 should count "A" as 1 and not 2.

Answer 1

Hi,

One easy way is to use If condition.

Are you trying to show like this table?

Use a dax like this, Similarily for each value. But this way cannot be used if there are multiple values.

Count C = IF('29Aug'[Category1]=="C"|| '29Aug'[Category2]=="C"||'29Aug'[Category3]=="C",1,0)

Try it out.

Answer 2

You can try duplicating the category columns and then unpivot the duplicated columns to use the actual columns for other calculations.

Then click on table, select value and OrderId.

Change count to count distinct.

You will see a table like below.

Hope it helps!