cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Max of one column based on user filter

Hello,

Long time being only a anonymous visitor and a self-learner, today I have to ask for help.

1st) The objective:

Based on the below example data:

 identifier rank Month A 1 2 A 1 2 A 2 3 A 3 4 A 3 4 A 4 5 A 4 5 B 1 4 B 2 5 B 3 6 B 3 6 C 1 3 C 1 3 C 2 4 C 3 5 D 1 6

And considering a user filter that includes the months 2, 3 and 4 I am tryng to get to this desired output

 Identifier Rank A 3 B 1 C 2

What it could be read as the max of column Rank based on the contextual filter.

The final output will be printed on a bar chart  that will count the number of Identifiers grouped by Rank.

2nd) The attempts:

The best attemp that I made was use a summarize, and create a new table, the problem was that the table is not filtered by the contextual filter, so I do not get the max of rank based on the filtered date, only the max of the rank. The code used was the following:

`SUMMARIZE(allselected(exampledata), exampledata[identifier],"max",CALCULATE(max(exampledata[rank]),allselected(exampledata)))`

Someone could please point me in the right direction, if this should be resolved by modeling or by a dax expression.

5 REPLIES 5
Community Support Team

## Re: Max of one column based on user filter

Hi

How does your desired output comes? Could you clarify more logic of this?

Regards,

Jimmy Tao

Frequent Visitor

## Re: Max of one column based on user filter

Hi Jimmy Tao,

The final output is the chart.

You could consider each identifier as a unique person, so the y-axis is the column "rank" and the x-axis is the count of people (identifier). However this count should only consider one person (identifier) in other words a distinct count. Besides this, to each person (identifier) the only associated rank (y-axis) should be the maximum rank considering the month filter from the user. So I believe that the count will "operate" over a table with only one record to each person (identifier).

I hope that I have clarified the desired output.

Best Regards,

Paulo

Community Support Team

## Re: Max of one column based on user filter

Hi Paulo1,

To achieve this, create a measure using DAX below:

`Result = CALCULATE(MAX(exampledata[rank]), ALLEXCEPT(exampledata, exampledata[identifier]), FILTER(exampledata, exampledata[Month] = 2 || exampledata[Month] = 3 || exampledata[Month] = 4))`

Regards,

Jimmy Tao

Visitor

## Re: Max of one column based on user filter

Hello v-yuta-msft

I work with Paulo1.

Thanks for your reply, but I need create a column because a measure can't use in group.

The final output will be printed on a bar chart  that will count the number of Identifiers grouped by Rank.

Regards,

Tiago Bohrer

New Member

## Re: Max of one column based on user filter

Interesting question. did you find the solution? I am also facing a similar problem

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 125 members 1,910 guests
Recent signins: