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
1,319 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,030 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,030 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
10,389 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 21, 2019 in Power BI by Avantika
• 1,520 points
1,453 views
0 votes
0 answers

How can I retrieve distinct values from multiple columns using Power BI?

How can I retrieve distinct values from ...READ MORE

Oct 23 in Power BI by Evanjalin
• 2,560 points
28 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,540 points
728 views
0 votes
1 answer

How is Power BI Report Server different from SQL Server Reporting Services and the Power BI online service

Power BI report server is deployed on-premises ...READ MORE

answered Oct 15, 2018 in Power BI by Hannah
• 18,540 points
1,230 views
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,490 points
3,546 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
1,546 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,030 points
1,125 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,030 points
2,607 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,030 points
4,359 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP