Count text values from different columns

0 votes

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.

Aug 26 in Power BI by anonymous
36 views

2 answers to this question.

0 votes

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.

answered Aug 29 by anonymous
• 26,020 points
0 votes

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!

answered Aug 29 by anonymous
• 26,020 points

Related Questions In Power BI

0 votes
1 answer

Dax: Sum of values from many columns in a row

Yes, you can. Try to "Unpivot other columns" ...READ MORE

answered Apr 3 in Power BI by Upasana
• 8,530 points
313 views
0 votes
1 answer
+1 vote
1 answer

How is power BI different from excel

Power series plug-ins you can do certain ...READ MORE

answered Sep 26, 2018 in Power BI by Hannah
• 14,070 points
36 views
0 votes
1 answer
0 votes
1 answer

Select Columns from table instead of removing afterwards in Power Query

Try this. let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = ...READ MORE

answered Oct 31, 2018 in Power BI by Shubham
• 13,290 points
198 views
0 votes
1 answer

How do you add a slicer from different table in Power BI?

In order to add a slicer from ...READ MORE

answered Mar 5 in Power BI by Phalguni
• 1,010 points
85 views
0 votes
1 answer

Add Column Count_of_people across two tables to get the count of people from a city.

Hi Anitha, 1. Merge both tables, using City ...READ MORE

answered Mar 22 in Power BI by Cherukuri
• 26,020 points
25 views
0 votes
1 answer

Adding values of two columns in two tables

Hi Anshuli, You can either create a separate ...READ MORE

answered Apr 10 in Power BI by Cherukuri
• 26,020 points
18 views
0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14 in Power BI by Cherukuri
• 26,020 points
40 views
0 votes
1 answer

How to add slicer from a different tables?

Hi, Join is normally performed to get ...READ MORE

answered Mar 1 in Power BI by Cherukuri
• 26,020 points
304 views