cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Paulo1 Frequent Visitor
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:

identifierrankMonth
A12
A12
A23
A34
A34
A45
A45
B14
B25
B36
B36
C13
C13
C24
C35
D16

 

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

IdentifierRank
A3
B1
C2

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.

chart.png

 

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
Community Support Team

Re: Max of one column based on user filter

Hi Paulo1,

 

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

 

Regards,

Jimmy Tao

Paulo1 Frequent Visitor
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
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))

Capture.PNG 

 

Regards,

Jimmy Tao  

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.

chart.png

 

Regards,

Tiago Bohrer

oliveiraguirafa New Member
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

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 125 members 1,910 guests
Please welcome our newest community members: