Issue With Parameter and Filtering

[SamForcum]

Hello!

I seem to be stuck in a catch-22 with using a parameter for subreporting. On one hand, I want to connect the parameter to the relationship model, and on the other measures aren't calculating correctly when I do so. Allow me to first explain what I'm looking to do, and then introduce the problems I'm experiencing.

Users of the report first select a semester and student type(s). Data regarding this initial point in time is displayed, such as entry major code. Depending on what semester is picked, flag measures named included_low and included_high create a window for which semesters are relevant for these students (i.e. Spring 2010 has included_low = 0 if the user chose Spring 2011). Also present is a subsection where users can select a future semester for a breakdown of those students at that time. The slicer controlling this function is driven by the parameter, which is a list of term codes.

When creating the parameter, I originally tried to use included_low and included_high within the GENERATESERIES code that populates it. I learned that parameters are created at the table level, and thus the parameter cannot be dynamically produced.

Why Add it to the Relationship Model?

Since the parameter can't generate on-the-fly, I filter the slicer housing it with the previously mentioned flags. Without a relationship between the parameter and the model, I'm not able to get the flags to work as filters on the visual. Also, creating calculated columns on the parameter to replicate the same logic seems stuck at the table level too - I tried making a flag which peeks into the chosen values, but with no success. 

I have connected the parameter to the relationship model, with a single direction. The parameter shouldn't be able to impact the model, but the model should be able to impact the parameter. Doing so causes the flags to work, and I no longer display nonsense terms in the slicer!

Why NOT Add it?

Despite the direction outlined above, measures are calculating unexpectedly which I need to display in the subreport section when the parameter is included in the relationship model.

For example, Total Graduates counts how many students have graduated in or before the selected semester of the parameter. Suppose 1 student graduated in Fall 2017, Fall 2018, and Fall 2019. If you select Fall 2018, you'd expect to see there were 2 total graduates. Instead, it says 1 person has graduated. Surprisingly, displaying tables of graduation records (ID and semester) are NOT having that problem (I've triple checked that the filter is applied to the table), and degrees earned before the selected semester are shown.

The code for Total Graduates is as follows:

Total Graduates =

VAR tg = COUNTROWS(CALCULATETABLE(DISTINCT(graduation[ID]), FILTER(graduation, graduation[ACADEMIC_PERIOD_GRADUATION] <= 'Select a Term'[Selected Term Value])))

RETURN IF(tg, tg, 0)

Additionally, I've tried to add "ALLEXCEPT(enrollment, enrollment[ACADEMIC_PERIOD])," as another filter. This is the field from the relationship model which connects to the parameter. 

Can anyone explain this mysterious behavior, or know how I can get the best of both worlds? Thanks for your help!

[kentyler]

For a problem with this many moving parts it would be helpful if you could post a small sample power bi file. Thanks.

[SamForcum]

Thanks for reaching out! I've built a test file with a few dummy records. The relationship between the parameter (named "select a term") and test-enrollment is currently inactive, so opening up the file will start with the measures working but the parameter slicer displaying all terms.

Here's the link to the test file!

[EduSurveys]

Hi @SamForcum , 

In looking at your data model, it doesn’t look like it’s set up in a star schema. That may be why you’re running into problems with your DAX measures. I think you're making things overly complicated as a result. If you haven’t already, I’d encourage you to check out the SQL BI training (https://www.sqlbi.com/training/), which is free and will explain how to convert your data model into the star schema so that your data model works. The one-to-many relationship from dimension tables (student, term codes, major code list) to fact table (enrollment) is critical for filtering to work. You can have repeating rows in your fact table, but you should have unique values in your dimension tables when you create your relationships. 

For example, for Student ID 2 who changed their major from Art to Math (if I understand that correctly, or maybe they were a double-major) you would only want one row for that student and create 2 columns, one for incoming major and one for current major, or similarly for 1st major and 2nd major.