Forum Discussion

ronlolololo's avatar
ronlolololo
Helper I
6 years ago
Solved

Calculate Max Return Text

Hi, I am playing around DAX right now.  Let's say i have a table have the following data. And I want to create a measure returning the state of highest value that day.

 

 

 

So first I tried. 

VAR ___Date = MAX(Table1[Date])


Return 

CALCULATE(MAX(Table1[State]),FILTER(Table1, Table1[Date] = ___Date))
 
The measure shows C, why is this ? (Even though three states have the exact same date and case)
 
Following this I tried another measure.

 

Measure = 

VAR ___Date = MAX(Table1[Date])

VAR ___MaxCase = CALCULATE(MAX(Table1[Case]),FILTER(Table1, Table1[Date] = ___Date))

Return

CALCULATE(MAX(Table1[State], FILTER(Table1, Table1[Date] = ___Date && Table1[Case] = ___MaxCase)))

 

This measure shows Error becuase of single value cannot be determined. Understandable, why this measure gives error but the first one is not?  

Need Help
  • andre's avatar
    andre
    6 years ago

    well, since your Date and Case values are the same for all three recoreds, filtering anything on those columns would not make any differences if you filter condition is the same as the filter will always return the same three records.  "C" is greater than "AAA" therefore, Max(State) will always return "C" as long as it can see all three records. 

     

    What are you expecting to see?

1 Reply

  • andre's avatar
    andre
    Memorable Member
    6 years ago

    well, since your Date and Case values are the same for all three recoreds, filtering anything on those columns would not make any differences if you filter condition is the same as the filter will always return the same three records.  "C" is greater than "AAA" therefore, Max(State) will always return "C" as long as it can see all three records. 

     

    What are you expecting to see?