Forum Discussion

tensixtyone's avatar
tensixtyone
New Member
5 years ago
Solved

Get last row from table by index

Hi, 

 

I'm having some problems with getting only the latest row in a table to show correctly on a card visual through a measure.

 

My dataset looks like this; 

 

So, what i want is the data from the last row, indexed as 4, to show in my card visuals.

 

To this, im using this formula for e.g the "High" column;

CALCULATE(LASTNONBLANK(table[High], 1),FILTER(ALL(table),table[index] = MAX(table[index]))).

 

However, the results are rather inconsistent, and i have had blank values returned before. Right now, the values that i get through to my card visuals are these;

Which are, if looking at the table and the data source excel sheet, not the latest numbers. 

 

The formula might be incorrect, im not too knowledgable about DAX yet, but from my understanding, this should work.

 

Why am i not getting the latest values, and why does it sometimes, when refreshing with new data, throw a (Blank) through to the visual?

 

EDIT: It seems, after further testing, that the measurement for "Medium" and "Low" are working as intended for now(for now, as "Low" showed a (Blank) on an earlier refresh), while the 2 others are taking data from the second to last row. The formula used in each one of these measurements are exactly the same.

Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi tensixtyone,

     

    The following formula should do the trick

    High Val = 
var _max_index = CALCULATE(MAX('Table'[Index]), ALL('Table'))
return 
CALCULATE(MAX('Table'[High]), FILTER(ALL('Table'), 'Table'[Index]  =_max_index))

     

2 Replies

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi tensixtyone,

     

    The following formula should do the trick

    High Val = 
var _max_index = CALCULATE(MAX('Table'[Index]), ALL('Table'))
return 
CALCULATE(MAX('Table'[High]), FILTER(ALL('Table'), 'Table'[Index]  =_max_index))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello tensixtyone 

     

    Try this measure :
    Max index with HighNotEmpty = CALCULATE(MAX(table[index]), FILTER(table, NOT(ISBLANK(table[High]))))

    Please accept it as solution if it solved your issue