Forum Discussion
How to visualize most recent known value (measure)
Hi,
background:
I have a dataset that tracks school performance based on attendance that categorizes the schools into low, medium, or high performing.
The dataset looks like this:
| School_ID | Date | Performance | score |
| 1013 | June 2020 | 0.5 | Medium |
| 1013 | July 2020 | 0.75 | Medium |
| 1013 | August 2020 | 1 | high |
| 1122 | June 2020 | 0 | 0 |
| 1122 | July 2020 | 0.7 | Medium |
| 1122 | August 2020 | 0.9 | high |
| 1135 | June 2020 | 0.6 | Medium |
| 1135 | July 2020 | 0.6 | Medium |
| 1135 | August 2020 | 0.7 | 0 |
I have a measure that returns the most recent known score of the schools:
Recent_Score =
VAR LatestDate =
max(attendance[Date])
VAR LatestValue =
MAXX(
FILTER(
attendance,
attendance[Date] = LatestDate
),
attendance[score]
)
Return
LatestValue
This works great when used across a date slicer so that we can always determine the most recent performance of that school based on the date (over time):
However, the issue I've run into and cannot seem to solve is that I would like to know: How many schools are performing in each category given the most recent known performance in that date period. I essentially want a stacked bar graph like below but instead of medium: 6, high:2, and 0: 1 it would show high: 2, Medium 1 based on the recent score in the matrix to the left.
I've attached a sample .pbix file here.
Help?
Thanks in advance.
Anonymous, try this measure:
Recent Score = VAR vMaxDate = LASTDATE ( ALLSELECTED ( attendance[Date] ) ) VAR vResult = CALCULATE ( COUNT ( attendance[score] ), vMaxDate ) RETURN vResult
6 Replies
- DataInsights
Super User
Anonymous, try this calculated column in the Values field well of the column chart:
Recent Score = VAR vSchool = attendance[School_ID] VAR vSchoolTable = FILTER ( attendance, attendance[School_ID] = vSchool ) VAR vMaxDate = MAXX ( vSchoolTable, attendance[Date] ) VAR vTargetRow = FILTER ( vSchoolTable, attendance[Date] = vMaxDate ) VAR vResult = MAXX ( vTargetRow, attendance[score] ) RETURN IF ( attendance[Date] = vMaxDate, vResult, BLANK () )HI DataInsights ,
Thanks for your work on this. I tested it and it works in returning the most recent score in the whole dataset. However, when I move the date slicer to anytime before the max date it returns blank. I would like for it to return the score for the max date defined by the date slicer. The example should return a column chart with 3 for medium since those are the scores in that date range.
Thanks in advance for helping me.
- DataInsights
Anonymous, try this measure:
Recent Score = VAR vMaxDate = LASTDATE ( ALLSELECTED ( attendance[Date] ) ) VAR vResult = CALCULATE ( COUNT ( attendance[score] ), vMaxDate ) RETURN vResult
- Ashish_Mathur
Hi,
I'd like to help. While you already have a Score column in your dataset, could you let me know the logic of assigning scores based on the performance? Is it that 0.76 and above is high? PLease clearly share the lower and upper value performance figure of each score.
Hi Ashish_Mathur ,
Thank you for your help.
The scores is supposed is based on a range:
"0" = "0",
<.5 = "low".
<.79 = "medium",
<= 1 = "High"
Thanks,
