DAX Group by - Direct Query

[Anonymous]

How to do group by using DAX in Direct Query Mode?

[TeigeGao]

Hi @Anonymous ,

There are many methods to do the group by using DAX in Direct Query mode:

1. Create a calculated table using the SUMMARIZE() function, please refer to the following DAX query:

table =
SUMMARIZE (
    historysales,
    historysales[Age],
    "Amount", COUNTROWS ( historysales )
)

2. Create a measure to represent it in a visual like table:

count =
CALCULATE (
    COUNTROWS ( historysales ),
    FILTER ( ALL ( historysales ), historysales[Age] = MIN ( historysales[Age] ) )
)

The result will like below: 

Best Regards,

Teige

[Anonymous]

@Teige Thank You for the example! I am not able to use Summarize and Groupby Functions in PowerBI Desktop Jan 2019 version for some reason those functions are greyed out. From your example in Calculate please kindly suggest if the Count 196 is the same for the ages 33, 34 then it would show count value as 196 for age 33 only in the result, it will omit age 34. How could I have both ages in the results of the count values is same. I am doing group by on 3 columns. Please suggest, I would highly appreciate your input! Thank You!

count =
CALCULATE (
    COUNTROWS ( historysales ),
    FILTER ( ALL ( historysales ), historysales[Age] = MIN ( historysales[Age] ) )
)

[Anonymous]

@Anonymous 

That's a very vague question.

There is a groupby function.

Thanks!
A