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
118 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
• 32,490 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
• 32,490 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
3,613 views
0 votes
1 answer

How do I label bar graph with different colors based on values from different slicers?

If the user selects only one value ...READ MORE

answered May 20, 2019 in Power BI by avantika
• 1,500 points
221 views
+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,410 points
95 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,450 points
1,204 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
441 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
• 32,490 points
110 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
• 32,490 points
187 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
• 32,490 points
246 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
• 32,490 points
2,103 views