Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

GROUPBY with Count

Hi,

 

I'm new to DAX. In SQL Server I have this query that returns a simple group by:

 

SELECT DISTINCT item_name, count(item_name) AS Item_count
FROM [MYDATABASE].[dbo].[MYTABLE]
GROUP BY item_name
ORDER BY Item_count DESC

 

The reason I need this DAX is so I can get the top 10 using the TOPN function. I can't seem to get my head around this, I've tried the DAX GROUPBY but keep getting errors. Is there someone who can point me in the right direction?

 

Regards,

Raymond

  • Anonymous's avatar
    Anonymous
    6 years ago

    GROUP BY in SQL is usually SUMMARIZE in DAX 🙂

     

    SUMMARIZE('MYTABLE', [item_name], "item_count", COUNTROWS('MYTABLE'))

     

    That will create a table with two columns (item_name and item_count). Use that as <table> in the TOPN function.

     

    Best Regards

    Ulf

2 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi,

     

    Rather than using GROUP BY as a DAX, you can use GROUP BY in Query Editor and achieve this. I have taken a sample data and attached screenshots in the order as follows:

     

    Once you get your grouped dataset, you can use Top 10 in your report.

     

    Let me know if this is helpful.

     

    Thanks.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    GROUP BY in SQL is usually SUMMARIZE in DAX 🙂

     

    SUMMARIZE('MYTABLE', [item_name], "item_count", COUNTROWS('MYTABLE'))

     

    That will create a table with two columns (item_name and item_count). Use that as <table> in the TOPN function.

     

    Best Regards

    Ulf