Forum Discussion

Helper I

6 years ago

Distinct Count in Query Editor... Is Group by the only solution

I have multiple columns that I need to do a distinct count within query editor. It seems that Group By is the only option... Do I need to preform a group by for every column, delete the extra colu...

Microsoft Employee

6 years ago

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

Helper I

6 years ago

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

thegusman
Helper I
6 years ago
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.
- v-juanli-msft
  Community Support
  6 years ago
  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.
  Distinct Count in Query Editor... Is Group by the only solution.pbix32 KB
artemus
Microsoft Employee
6 years ago
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.