Forum Discussion
Dynamic Table Filtering Based on Max in a Group according to Date Slicer
I have a Student_Statuses table with following structure, it primarily maintains history of student as he/she move back and forth among different academic programs, i.e. he might be active, suspended etc. This table also has other dimension columns in it like program name etc. One student sample is shown in screenshot.
Also, I have a date slicer as well based on Status Effective Date, as shown.
My goal is to have the existing table filtered for only maximum Admission Status ID for each student based on selected date range. I have already created following measure that gives me maximum Admission Status ID for each student for selected data range.
Max_Adm_Status = CALCULATE(MAX(Student_Statuses[Admission Status ID]),FILTER(ALLSELECTED(Student_Statuses), Student_Statuses[Student ID] = MIN(Student_Statuses[Student ID])))
I get following perfectly fine measure that tells me about latest status for each student for given date slicer, as shown: -
Only issue is I am not able to filter out the data that's not relavant. I have tried creating flag by comparing Adm Status ID to Measure but it raises an error od circular dependency.
isMax = IF(Student_Statuses[Admission Status ID] = [Max_Adm_Status], 1, 0)
My question is how can I create a dynamic measure that helps me filtering all irrelevant records in this table, such we only have one row for a student based on max status ID. I have also tried SUMMARIZE/GROUPBY, but off course they don't get updated dynamically. I will be really thankful 🙂
hi Anonymous
If so, just adjust as below:
1. adjust the formula of measure [Max_Adm_Status ]
Max_Adm_Status =
CALCULATE (
MAX ( Student_Statuses[Admission Status ID] ),
FILTER (
ALLEXCEPT ( Student_Statuses, Student_Statuses[Status Effective Date] ),
Student_Statuses[Student ID] = MAX ( Student_Statuses[Student ID] )
)
)2. and use [Admission Status] filed from another table 'Students' as a slicer:
Regards,
Lin
11 Replies
hi Anonymous
Just adjust the isMax formula as below to create a Measure
isMax = IF(MAX(Student_Statuses[Admission Status ID]) = [Max_Adm_Status], 1, 0)Then drag it to visual level filter set is "1"
Regards,
Lin
hi v-lili6-msft , thanks for replying, I did create the measure as you said and it turn out to be giving me 1 and 0 as I needed.
thanks, but I think it will only be usefull only in the evaluation context of that table visual.
For example how can I use it to count the number of students from that filtered table, i.e. first filtered on the basis of date (via slicer) and then with only rows with max status ID? I created following measure but it is not giving correct count? Should it? Is my Max_Adm_Status measure fine?
Measure I am using for Student
No of Students = CALCULATE(DISTINCTCOUNT(Student_Statuses[Student ID]), FILTER(KEEPFILTERS(Student_Statuses), [isMax] = 1)hi Anonymous
Yes, it will work in this row text.
For your requirement, you could try to use SUMX to achieve it.
You could try it by yourself, if you still have the problem, please share a sample pbix file and expected output.
Regards,
Lin
Anonymous how these tables are connected? Also you last expression should be a measure but seems like you are adding it as a column. To use it as a measure, you should add aggregation like Min or Max to first column of the IF condition.
thanks parry2k for your reply. Sorry I have updated the question so we have only one table, i.e. Student_Statuses.
Yes, isMax should be measure which I am not able to create successfully due to exact reason you mentioned. Do you think it would be correct to use an aggregation, i.e. Min etc.
My goal is to get a measure that gives me 1 for row with current max Status ID for each student and 0 for rest of statuses for each student.
You have calculated that column and based on that again you trying to match. The first formula is filtering, it means it is giving output at one. In case you need one and 0, you should have a formula created for that. You can use Rank, earliest to get such formula.
Refer
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
