topic Re: Summarize on one column but select another column in DAX Commands and Tips

Summarize on one column but select another column

PranjalSaxena — Tue, 02 Jul 2024 14:57:08 GMT

I have a table as follows

State	Constituency	Party	Votes
UP	Noida	SP	1000
UP	Noida	BJP	500
UP	Noida	INC	300
Gujarat	Dholera	SP	300
Gujarat	Dholera	BJP	2000
Gujarat	Dholera	INC	400

I need to create a calculatedtable which should show the 'Party' with maximum 'Votes' in each 'State' and 'Constituency' combination.

If I use Summarize() I am only able to get a table like

State	Constituency	Max Votes
UP	Noida	1000
Gujarat	Dholera	2000

What I need is like this:

State	Constituency	Party
UP	Noida	SP
Gujarat	Dholera	BJP

I even tried GroupBy() function using CurrentGroup() using the below formula:
WinnerParty =

GROUPBY(VotingResults, VotingResults[State], VotingResults[PC Name], "WinnerParty", SELECTCOLUMNS(FILTER(CurrentGroup(), VotingResults[Total Votes] = MAXX(CURRENTGROUP(), [Total Votes])),[Party]))

Here, I am trying to FILTER() the row in currentgroup() having maximum votes and then SelectColumn() the 'Party' Column.
But I am getting error:
Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

Re: Summarize on one column but select another column

rajendraongole1 — Tue, 02 Jul 2024 15:54:15 GMT

Hi PranjalSaxena - Can you try the below calculated table in your model.

WinnerParty =
ADDCOLUMNS(
SUMMARIZE(
VotingResults,
VotingResults[State],
VotingResults[Constituency],
"Max Votes", MAX(VotingResults[Votes])
),
"Party",
SELECTCOLUMNS(
TOPN(
1,
FILTER(
VotingResults,
VotingResults[State] = EARLIER(VotingResults[State]) &&
VotingResults[Constituency] = EARLIER(VotingResults[Constituency]) &&
VotingResults[Votes] = EARLIER([Max Votes])
),
VotingResults[Votes],
DESC
),
"Party", VotingResults[Party]
)
)

It works please check.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Re: Summarize on one column but select another column

Anonymous — Wed, 03 Jul 2024 14:48:06 GMT

though one solution is accepted, i am giving one other solution,

first count the constituency numbers,

Then use the topn