Max of one column based on user filter

[Paulo1]

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.

[oliveiraguirafa]

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

[v-yuta-msft]

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  

[Tiagodanielfb]

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

[v-yuta-msft]

Hi Paulo1,

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

Regards,

Jimmy Tao

[Paulo1]

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