DAX TOP 1 COUNT of GROUPBY

Question

I want to have a Card in Power BI with the result of the select

SELECT TOP 1 [COLUMN] FROM [Table]
GROUP BY [COLUMN]
ORDER BY COUNT([COLUMN]) DESC

How can I do something like this in dax?

[Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/2029983) — Thom A, Nov 26 '20 at 12:37
Check here for more info- [link]( https://learn.microsoft.com/en-us/dax/groupby-function-dax) — GRVPrasad, Nov 26 '20 at 14:53
Related: https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column — Alexis Olson, Nov 26 '20 at 15:37

score 0 · Answer 1 · answered Nov 26 '20 at 13:30

0

create in powerbi a new table by dax:

Top1 = TopN(1, VALUES(Table__[Column1]), Table__[CountOf], DESC)

where

CountOf = CountOf = count(Table__[Column1])

answered Nov 26 '20 at 13:30

msta42a

score 0 · Answer 2 · answered Nov 26 '20 at 21:29

this is equivalent the maximum value of [COLUMN], that can be easily obtained with

MAX(Table[COLUMN])

another solution is using LASTNONBLANKVALUE

LASTNONBLANKVALUE(Table[COLUMN], Table[COLUMN])

that returns a scalar, otherwise LASTNONBLANK returns a table of a single row and a single column

LASTNONBLANK(Table[COLUMN], Table[COLUMN])

score 0 · Accepted Answer · edited Nov 30 '20 at 05:06

0

My problem was solved by making a

SingleLeads = DISTINCTCOUNT(Fact_ProductRequests[COLUMN])

I figure this out after checking that there were repeated values in the column.

It was probably, the desperation that settled in not getting the results I wanted, that made me open this question.

Thank you all.

edited Nov 30 '20 at 05:06

marc_s

answered Nov 27 '20 at 12:43

Mac

3 Answers3