Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

counting the values from more than one column in the same table

Hi,

 

I'm looking for the solution how can I count the values from more than one column in the same table? eg. I have a list of people and each of them has been asked for three favourite items from the list, and now I would like to count, how many times each item has been voted.

 

In excel I would use countif formula and then indicate the range. Do you know if it is possible in Power BI?

 

Thank you in advance for your help.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

 

You should first Unpivot these Columns so that all the 3 Columns are under one column. You could do this by using PowerQuery in Power BI

Open PowerBI File-> Edit Queries -> Under Power Query Choose the Correct Table ->Select the Three columns in the Table by Clicking on the Table Headers of all 3 columns -> Click on Transform Ribbon on the Top ->Unpivot Column

 

Once you have unpivoted the Columns Click on Close&Apply.

 

Your new Table should be loaded into the Model.

 

Now Select Modelling -> Click on Create new Table -> Insert the Below Code

[Your_Table_Name]:= SUMMARIZECOLUMNS(TableA[Answer],"Count", COUNT(Answer))

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hey, please see data, maybe this will be helpful:

Answer 1Answer 2 Answer 3
bluewhite 
whitegreenbrown
blueyellow 
red  
blackred 

 

And the solution I would like to get is:

OptionVotes
black1
blue2
brown1
green1
red2
white2
yellow1
Anonymous
Not applicable

Hi,

 

You should first Unpivot these Columns so that all the 3 Columns are under one column. You could do this by using PowerQuery in Power BI

Open PowerBI File-> Edit Queries -> Under Power Query Choose the Correct Table ->Select the Three columns in the Table by Clicking on the Table Headers of all 3 columns -> Click on Transform Ribbon on the Top ->Unpivot Column

 

Once you have unpivoted the Columns Click on Close&Apply.

 

Your new Table should be loaded into the Model.

 

Now Select Modelling -> Click on Create new Table -> Insert the Below Code

[Your_Table_Name]:= SUMMARIZECOLUMNS(TableA[Answer],"Count", COUNT(Answer))
Anonymous
Not applicable

@Anonymous thank you for this solution, it works but I have more questions and answers in my table and this solution multiplies the rows causing errors in other calculations. Do you think is there any other solution?

Anonymous
Not applicable

Hi,

 

Since I have no context in the other calculations, I dont find myself in a position to answer.

 

However, if this Solution has solved your Issue. Do Accept as Solution. 

 

Cheers

Anonymous
Not applicable

Thank you, I've decided to move each question with its answers to the separate table keeping IDs, do you know what needs to be done when update the data? Should I go  all above steps each time?

 

Cheers

Anonymous
Not applicable

it doesn'y work at the end 😞

I have an alert:

Column 'ID' in Table 'Q1' contains a duplicate value '1' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

 

Any idea without unpivot?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.