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

[Anonymous]

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.

[Anonymous]

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

Answer 1	Answer 2	Answer 3
blue	white
white	green	brown
blue	yellow
red
black	red

And the solution I would like to get is:

Option	Votes
black	1
blue	2
brown	1
green	1
red	2
white	2
yellow	1

[Anonymous · Accepted solution]

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]

@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]

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]

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]

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?