Dynamic Table Filtering Based on Max in a Group according to Date Slicer

[Anonymous]

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 🙂

[v-lili6-msft]

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

[Anonymous]

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)

[v-lili6-msft]

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]

thanks @v-lili6-msft and @parry2k . I tried my self but couldn't figure it out. I will really appreciate if you can help me. I have shared pbix.

I am getting correct overall distinct student count because it is directly filtered by date but wrong count of students among programs and statuses over the period of time. I have inserted image for desired output as well.

Also, I have added an other duplicate table (Students) as an effort to have a filtered the table based on [Student ID + Admission Status ID].

On second page I have our previously worked out table visual with Max_Adm_Status & isMax Measures.

My goal is to get one row (with max Admission Status ID) for each student according to the range selected in date slicer. Measure No of Students should use that filtered data whereever referred.

Desired output is calculated by using a SQL query.

Link to pbix in Google Drive 

[v-lili6-msft]

HI @Anonymous 

For your case, just try this formula instead of "No of Students = DISTINCTCOUNT(Students[Student ID])"

Measure = SUMX(VALUES(Student_Statuses[Student ID]),[isMax])

Result:

Regards,

Lin

[Anonymous]

thanks @v-lili6-msft I really appreciate you helping me out.

I tried & and it worked. Only issue is that it doesn't cop with slicers, for now I have the only Admission Status slicer but there can more for same table.

For example when I applied Active status flag, measure changed back to the way it was earlier.

I tried to wrap Measure's VALUES function with KEEPFILTERS / ALLSELECTED, but it didn't work.

Also in measure by program, same thing happended.

Link to updated PBIX in Google Grive 

[v-lili6-msft · Accepted solution]

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

[parry2k]

@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.

[Anonymous]

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.

[amitchandak]

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

[Anonymous]

thank @amitchandak for replying,

I think RANKX, when used as measure, need a measure to rank over. As I need to rank statuses based on Admission Status ID (which is a column, unless I apply some agg etc.), so it doesn't allow me to create measure.

Can't create following measure, can you please help me point issue?

Status_Rank = RANKX(ALLSELECTED(Student_Statuses[Student ID]), Student_Statuses[Admission Status ID])

Also I tried using MIN & MAX with status ID but this, as expected, always returns 1. Look forwards to your reply 🙂