topic Re: Substitute Value in Filter in DAX Commands and Tips

Substitute Value in Filter

Anonymous — Sun, 02 Apr 2023 21:00:22 GMT

I have a simple measure that counts the number of enrollments for three unique periods, Fall, Spring and Summer. Using SelectedValue I can tell what priod has been requested and only want to return those entries. My measure is as follows and I would like to substitute the term variabale (Fall, Spring or Summer) in the FIlter line.

I have have no sucesss, if you enter "Fall" it works great, so I need a method to have Quotiations in the substution,

CALCULATE(COUNTROWS(Enrollment),

All(Enrollment[Deposit Intent to Enroll Date/Time]),

Enrollment[Program] = _program,

Enrollment[Start Term and Year] = _termyear,

Enrollment[Deposit Intent to Enroll] = "Yes",

Enrollment[Deposit Intent to Enroll Date/Time] <=enddate,

FILTER('Enrollment',CONTAINSSTRING('Enrollment'[Start Term and Year],_term))))

Amy and all suggestions willbe greatly appreciated.

Re: Substitute Value in Filter

tamerj1 — Sun, 02 Apr 2023 21:55:47 GMT

Hi Anonymous

manually create 'Terms' a disconnected single column table that contains fall, spring, summer

in the measure define the variable

VAR _term = SELECTEDVALUE ( Terms[Term] )

Re: Substitute Value in Filter

Anonymous — Mon, 03 Apr 2023 14:20:45 GMT

tamerj1,

Thank you very much for your quick reply,

I made a quick test and am still having an issue.

Here are the steps I have taken.

Made a quick Term table which looks like this

Added the following selection statement, which returns “Fall” VAR _term = SELECTEDVALUE(FallTerm[Term])
Add _term to filter statement and there was no return of column data.

Removed the quotations and there was no return of column data.

Hard coded “Fall” in filter statement and column data is returned.

I have to assume the only two items that are causing the problem is that created the Term table incorrectly, or something else needs to done to handle the quotation marks.

I really appreciate your time with this issue, it appears to be a simple problem , unfortunately I have not been able to find the answer,

Pjb120

Re: Substitute Value in Filter

tamerj1 — Mon, 03 Apr 2023 14:25:05 GMT

Anonymous