Forum Discussion

thegusman's avatar
thegusman
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...
Need Help
thegusman's avatar
thegusman
Helper I
6 years ago
FruitVegetableFruitCountVegetableCount
AppleCarrot12
OrangeCarrot32
PeachCucumber22
PeachTomato21
OrangeOnion31
OrangeCucumber32
thegusman's avatar
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's avatar
    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.