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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PS23
Regular Visitor

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:

 

LanguagePrimary CountrySecondary CountryTertiary Country
EnglishEnglandUSAScotland
SpanishSpainMexicoUSA
FrenchFranceHaitiCanada

 

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 6
PS23
Regular Visitor

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

DateLanguagePrimary CountrySecondary CountryTertiary Country
1/1/01EnglishEnglandUSAScotland
2/2/02SpanishSpainMexicoUSA
3/3/03FrenchFranceHaitiCanada

 

My expected output looks like

 

England1
USA2
Scotland1
Mexico1
France1
Haiti1
Canada1

 

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

@PS23 

select date column and unpivot other columns in pq

1.PNG2.PNG

3.PNG

is this what you want?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




That looks like the right output that would let me run the same date filter on the calculated table as the original. Can you explain how you got to that point from the original table, please?

@PS23 

select date column and unpivot other columns in pq,then you will get the new layout





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




My primary table has about 800 rows (anticipating it has 1500 by year's end), and about 60 columns. If use only the needed data in a new table, I still wind up with 32 columns before the unpivot. The current solution for using distinct values from a union of the country-equivalent-columns, then using the countrows function (if I'm remembering that right) gets me 80% of the way there, but then I wind up with a visual slicer for time that works on the primary table but not the second table (something about lost lineage according to the guides I'm reading). 

 

I'm hoping there's a simple few lines of instructions I can include in the number-of-times-column in the second table, so that it can first filter the primary table based on a creation date, and then countrows. But that might be best asked in a new question since it's off the topic from the original posted here.... 

ryan_mayu
Super User
Super User

could you pls provide some sample data and expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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