Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Paulo1
Regular 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
oliveiraguirafa
New Member

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

v-yuta-msft
Community Support
Community Support

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  

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

v-yuta-msft
Community Support
Community Support

Hi Paulo1,

 

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

 

Regards,

Jimmy Tao

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.