Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Substitute Value in Filter

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.

 

DAX code
  • tamerj1's avatar
    tamerj1
    3 years ago

    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] )

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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] )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

       

      1. Made a quick Term table which looks like this

       

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

       

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

       

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