The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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_ID | Attribute_type_ID | Person_ID | Attribute_date | Attribute_value | Ordering |
1 | 1 | 1001 | 5 Feb 2015 | 10 | 1 |
2 | 1 | 1001 | 8 Mar 2018 | 8 | 2 |
3 | 1 | 1001 | 10 Sept 2020 | 6 | 3 |
4 | 2 | 1001 | 8 Mar 2018 | 3 | 1 |
5 | 2 | 1001 | 6 Sept 2016 | 5 | 2 |
6 | 3 | 1001 | 10 Sept 2020 | ABB | 1 |
7 | 3 | 1001 | 8 Mar 2018 | CDD | 2 |
8 | 3 | 1001 | 6 Sept 2016 | BCC | 3 |
9 | 1 | 1002 | 2 Feb 2013 | 8 | 1 |
10 | 1 | 1002 | 4 Oct 2016 | 4 | 2 |
11 | 2 | 1002 | 14 Feb 2017 | 2 | 1 |
12 | 3 | 1002 | 14 Feb 2017 | ABC | 1 |
I'd like the matrix to show the data for the full date range as follows:
Person_ID | Attribute 1 | Attribute 2 | Attribute 3 |
1001 | 10 | 3 | ABB |
1002 | 8 | 2 | ABC |
And if the slider was change to show data from the start of 2015 for example:
Person_ID | Attribute 1 | Attribute 2 | Attribute 3 |
1001 | 8 | 3 | ABB |
1002 | 4 | 2 | ABC |
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.
Solved! Go to Solution.
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])
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.
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.
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])
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 🙌
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.
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.