Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamic Min and Max Date Field Based on Values From Slicer Selection

I am trying to get the first score, and the last score based on the dates selected in slicer. I created a date table with DAX and joined to this table one to many, then created a measure to get the min and max date from ALLSELECTED (using between dates slicer). But when I try to use those fields as variables in a dax measure, I am getting errors such as only expecting one value. My end goal is to get the average of the first scores and last scores per wound to see if they have improved or not. This would be a great start, thanks

 

-----------------------TABLE: If Slicer selection dates between 1/5/2020 and 1/23/2020
 

 

---------------DAX MEASURE SO FAR:

 

Last Visit Date =
 
VAR patient = 'Query1'[PatientID]
VAR wound = 'Query1'[WoundSite]
VAR startdate = [Start Date Measure]
VAR enddate = [End Date Measure]

RETURN
MAXX( FILTER(
    ALL('Query1'),'Query1'[PatientID] = patient
          && 'Query1'[WoundSite] = wound
          && Query1[VisitDate] <= enddate
          && Query1[VisitDate] >= startdate
 
   ),Query1[VisitDate])
 
ALLSELECTED
date slicer
VAR

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    Can you provide an example based on the dat you show wit the expected result?

    Where/how would you want to use that measure?

    The relationship is between which cilumns exactly??

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I am joining Dates[Date] to Query1[DOS]   --(Date of Service)

      I would be using the measures in cards and pivot them to put on bar chart as a single value, always the average of total. Just need the inital and final scores never aggregated