Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX Group by - Direct Query

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

Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous 

    That's a very vague question.

    There is a groupby function.

     

    Thanks!
    A

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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] ) )
)