GROUPBY with Count

[Anonymous]

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 · Accepted solution]

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

[Pragati11]

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.