Count text values from different columns

+2 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, 2019 in Power BI by anonymous
184 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, 2019 by anonymous
• 33,010 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, 2019 by anonymous
• 33,010 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, 2019 in Power BI by Upasana
• 8,620 points
5,755 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
• 18,570 points
161 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,480 points
1,983 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, 2019 in Power BI by Phalguni
• 1,020 points
691 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, 2019 in Power BI by Cherukuri
• 33,010 points
191 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, 2019 in Power BI by Cherukuri
• 33,010 points
415 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, 2019 in Power BI by Cherukuri
• 33,010 points
448 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, 2019 in Power BI by Cherukuri
• 33,010 points
2,954 views