Forum Discussion
Counting values across multiple rows based on primary column
I'm new to PowerBI and in reading through a lot of the answers to other similar topics, there are a lot of strings of codes but not great details on the how and why of the solutions. Appreciative of any help.
I have a large table with many columns, and I'm trying to create a visualization based on a subset. It's similar to the below chart. I've created a new table to remove duplicates and create a column of the distinct values, and I'm trying to get a count of the instances of that first column's values appearing across all other rows:
| Language | Primary Country | Secondary Country | Tertiary Country |
| English | England | USA | Scotland |
| Spanish | Spain | Mexico | USA |
| French | France | Haiti | Canada |
So I have another table with the first column set to have a list of distinct values (Canada, England, Scotland, etc.), What I need is a way to count the instances of each country within the dataset and across multiple rows, so I wind up with a list that has a country name and a value of how many times total it appears. My actual table has a lot more than 3 columns.
Thank you for any advice or possible solutions!
6 Replies
could you pls provide some sample data and expected output?
I managed to track down part of an answer but it didn't solve the entire issue. I can expand on the sample table I included but that's the only example I can give. My half-solution is calculating a union table that combines all the primary-secondary-and so on columns into one, then counts the rows that match the distinct values drawn from those columns.
So while my first table looks like
Date Language Primary Country Secondary Country Tertiary Country 1/1/01 English England USA Scotland 2/2/02 Spanish Spain Mexico USA 3/3/03 French France Haiti Canada My expected output looks like
England 1 USA 2 Scotland 1 Mexico 1 France 1 Haiti 1 Canada 1 I'm getting the second table with the calc - union approach, but the lingering issue is that my original data table also has a date field and once I create the visual based on the calculated union table, it doesn't filter based on the date, which is only affecting the original table.
So I'm hoping for a way to calculate the same output values in a way that maintains the relationship between the primary table and the calculated union table so that filters applied to the primary with also affect visuals made from the calculated. Or some other way to simulate the effect.
I tried creating a bar chart and dragging all the response choices (primary, secondary, .... all the way to 30) into both the axis and values fields and then changing the colors to all be the same to it looks like one bar and not 30 rainbow pieces, but that's not ideal.
Sorry if this isn't super clear, I'm stumbling my way through this project
