Forum Discussion
Counting values across multiple rows based on primary column
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
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?
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....