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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
tim_mountford
New Member

Filter minimum value per category within date range

Hi,

 

I'd really appreciate some help trying to create a measure to select the minimum value per category within a selected date range.

 

The dataset contains rows with different attribute measurements for different individuals over time. I've got the following columns:

  • Row_ID: Unique ID for each row
  • Attribute_type_ID: An integer ID identifying the type of attribute measurement
  • Person_ID: An integer ID identifying the person the measurement is taken from
  • Attribute_date: The date at which the measurement was taken
  • Attribute_value: The measurement value recorded
  • Ordering: Ranking showing the importance of each value, I aim to display the values with the lowest 'ordering' value

I'd like to display the data in a matrix with a row for each person and a single value for each attribute type. The ranking of each attribute type differs as I need to display the highest value for one attribute type, but the lowest value for others; So I need to display the attribute value that corresponds to the lowest 'ordering' value. I'd also like to include a date slicer linked to the Attribute_date variable such that if the value with Ordering = 1 is outside of the date range, the value with Ordering = 2 is shown.

 

The dataset looks something like this:

Row_IDAttribute_type_IDPerson_IDAttribute_dateAttribute_valueOrdering
1110015 Feb 2015101
2110018 Mar 201882
31100110 Sept 202063
4210018 Mar 201831
5210016 Sept 201652
63100110 Sept 2020ABB1
7310018 Mar 2018CDD2
8310016 Sept 2016BCC3
9110022 Feb 201381
10110024 Oct 201642
112100214 Feb 201721
123100214 Feb 2017ABC1

 

I'd like the matrix to show the data for the full date range as follows:

Person_IDAttribute 1Attribute 2Attribute 3
1001103ABB
100282ABC

 

And if the slider was change to show data from the start of 2015 for example:

 

Person_IDAttribute 1Attribute 2Attribute 3
100183ABB
100242ABC

 

I created a calcualted column (IsLowestRanking) which flags the row with the lowest ordering value, then filtered the matrix to show values with IsLowestRanking = 1. This works perfectly for the full date range, but as the calculated column works at a table level, it doesn't change with the date slider and the values for Attribute1 would just appear blank.

 

IsLowestRanking = IF(
[ordering]=MINX(FILTER(ALL('Table'),'Table'[Person_ID]=MAX('Table'[Person_ID])),[ordering]),1,0)

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @tim_mountford ,

 

Please try this measure.

Measure = var a=FILTER(ALLSELECTED('Table'),[Person_ID]=SELECTEDVALUE('Table'[Person_ID])&&[Attribute_type_ID]=SELECTEDVALUE('Table'[Attribute_type_ID]))
var b=MINX(a,[Ordering])
return MAXX(FILTER(a,[Ordering]=b),[Attribute_value])

vtangjiemsft_0-1681802052333.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

View solution in original post

4 REPLIES 4
tim_mountford
New Member

Hi Neeko, @Anonymous 

Thank you so much for your help! Unfortunately, I don't think your solution works. For the full date range I would want to display the values for each person with 'ordering' = 1. Whereas if I only look at data from 2015, the attribute 1 values with ordering = 1 for each person in the above example fall outside outside of the date range and I'd want to then show the values with ordering = 2.

So I'm not looking for the minimum value in the attribute_value column, but rather the value associated with the minimum ranking. This is because for attribute 1 for example, I'm looking for the highest value, for attribute 2 I'm looking for the lowest and for attribute 3 I might be looking for the value closest to their first visit.

 

Ideally the measure would be a flag that is 1 for the rows with the minimum ordering value in the date range and 0 otherwise, that way I can filter the matrix with Measure = 1.

Anonymous
Not applicable

Hi @tim_mountford ,

 

Please try this measure.

Measure = var a=FILTER(ALLSELECTED('Table'),[Person_ID]=SELECTEDVALUE('Table'[Person_ID])&&[Attribute_type_ID]=SELECTEDVALUE('Table'[Attribute_type_ID]))
var b=MINX(a,[Ordering])
return MAXX(FILTER(a,[Ordering]=b),[Attribute_value])

vtangjiemsft_0-1681802052333.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Thank you so much Neeko! This is exactly what I was looking for 🙌

Anonymous
Not applicable

Hi @tim_mountford ,

 

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create a measure. 

MinAttribute = CALCULATE(MIN('Table'[Attribute_value]),FILTER(ALLSELECTED('Table'),'Table'[Attribute_type_ID]=MAX('Table'[Attribute_type_ID]) && 'Table'[Person_ID]=MAX('Table'[Person_ID])))

(3) Then the result is as follows.

vtangjiemsft_0-1681715846646.png

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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