cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to filter a table based on a column's highest value

Hi,

 

I am trying to calculate the total quantity by Quality from a table called "Data", but filtering on the highest value in Period (in this case, 201906).  The purpose is to calculate the variance for each quality (highest period/lowest period).

 

In other words, I would like to get for each product Quantity [Period 201906] / Quantity [Period 201902]

 

DAX Max value.jpg

Source file

 

I have tried different formulas but I'm struggling with the filter context.

 

Can anyone help me?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: How to filter a table based on a column's highest value

Please use the same link as above.

Best
D.

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: How to filter a table based on a column's highest value

Create a DAX formula that goes something like this:

 

Measure IsHighest = 
  VAR __table = SUMMARIZE(ALLSELECTED('Table'),[Quality], [Period],"__Quantity",SUM([Quantity]))
  VAR __max = MAXX(__table,[__Quantity])
  VAR __period = MAXX(FILTER(__table,[__Quantity] = __max),[Period])
RETURN
  IF(MAX([Period]) = __period,1,0)

Then just fiilter on this being 1


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: How to filter a table based on a column's highest value

Again, please resist the temptation to do it in DAX. Use Power Query because this is the right tool for the job.

Best
D.
Highlighted
Helper I
Helper I

Re: How to filter a table based on a column's highest value

How would you proceed, then, with Query M?

Highlighted
Anonymous
Not applicable

Re: How to filter a table based on a column's highest value

Highlighted
Helper I
Helper I

Re: How to filter a table based on a column's highest value

Thanks @Anonymous  and @Greg  for your solutions. My description of the issue is probably not accurate enough. What I am looking for is not retrieving the minimum quantity for each quantity. I want to retrieve the quantity for the Maximum value in "Period".

 

So for Quality "A", I want to retrieve the quantity where the value "Period" is the highest, so 10, not 13.

 

Then I would like to do the same for the minimum Period (so retrieve Quantity 5 for Quality A in @Anonymous 's example).

 

In the end, I would divide the Quantity for the highest Period by the Quantity for the lowest Period, for each Quality. That would give me an index, which is the scope of all the above.

 

I thought that DAX was the right language to solve this question but I didn't figure out how, despite may trials.

 

Thanks again for your help!

Highlighted
Anonymous
Not applicable

Re: How to filter a table based on a column's highest value

OK. So you need another table (created in PQ) that for each quality will store the quantities for the latest and oldest period.

 

I'll post a link to a file shortly.

Best
Dare

Highlighted
Helper I
Helper I

Re: How to filter a table based on a column's highest value

Yes, I think that should be a way to go. 🙂

Highlighted
Anonymous
Not applicable

Re: How to filter a table based on a column's highest value

Please use the same link as above.

Best
D.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.