2 Replies Latest reply on Jun 20, 2018 1:52 PM by Scott Schmeling

    Use a date based on a Measure Name

    Scott Schmeling

      Hi,

       

      I will upload something later tonight but seeing if anyone has a quick answer.  I have a SQL Query which is pulling in the following information:

       

      Client #     Location     Internal Referral Date     External Referral Date     Enrollment Date     Graduation Date     Class Date

       

      I am creating a snapshot dashboard- to show what each of the numbers are for a specific month.

       

      Basically I am using some form of LOD to get the counts:

      {Fixed(Datepart('month', [Internal Referral Date])), (Datepart('month', [Internal Referral Date])), [Location]: CountD([Client #])}  For internal Referral counts

      {Fixed(Datepart('month', [External Referral Date])), (Datepart('month', [External Referral Date])), [Location]: CountD([Client #])} For External Referral Counts

       

      And so on and So on.

       

      I want to create a table for the dashboard which shows:

       

      Header 1Header 2
      Internal Referrals75
      External Referrals205
      Enrollments43
      Graduations10
      Classes37

       

      So I need to find a way to tell it:

       

      If Measure Name = "Internal Referrals" Then [Internal Referral Date]

      ElseIf Measure Name = "External Referrals" Then [External Referral Date]

       

       

      Then I will set that equal to a parameter so I can select the month that the dashboard is showing.

       

      I know you can't use Measure name as part of the formula.  Any Suggestions on how to do this?

       

      As I said I will post a workbook tonight for it, but have to create one as the data I am using is HIPAA compliant.

       

      Thanks,