Forum Discussion
Distinct Count in Query Editor... Is Group by the only solution
Thanks Artemus,
I may have mispoke on what I was trying to accomplish.
Column 1 contains many different values. I want to see the frequecy of every value within that column in another column. I could do that with a group by Count, and add All Rows, but my problem is that there are many columns in that table that I also need the value's frequency.
I am dreading the thought of doing a group by 10+ times, and then cleaning. Especially since my data is 300k+ rows.
What is the format of the table you want to have at the end? E.g.
| ColumnName | Term | Count |
| Column1 | Value1 | 4 |
| Column1 | Value2 | 6 |
| Column2 | Value1 | 1 |
Fruit Vegetable FruitCount VegetableCount Apple Carrot 1 2 Orange Carrot 3 2 Peach Cucumber 2 2 Peach Tomato 2 1 Orange Onion 3 1 Orange Cucumber 3 2 I realize this table is very simple, but I have many columns that need to be counted.
I used a Text.Combine to group the columns that need to be counted together, since there are so many.
Hi thegusman
You could create calculated columns in Data model view,
fruit count = CALCULATE( COUNT('Table'[Fruit]),ALLEXCEPT('Table','Table'[Fruit])) vege count = CALCULATE( COUNT('Table'[Vegetable]),ALLEXCEPT('Table','Table'[Vegetable]))or create measures which can change with slicers
fruit count m = CALCULATE( COUNT('Table'[Fruit]),FILTER(ALLSELECTED('Table'),'Table'[Fruit]=MAX('Table'[Fruit]))) vege count m = CALCULATE( COUNT('Table'[Vegetable]),FILTER(ALLSELECTED('Table'),'Table'[Vegetable]=MAX('Table'[Vegetable])))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ok, you do realize that your table isn't normalized though? E.g. In your example above, you have Orange 3 times, and for all 3 times FruitCount is 3. This can cause issues when you aggergate it on the visualization layer.
It is possible to do this, but it may not be what you want.