Forum Discussion

timelez's avatar
timelez
New Member
5 years ago
Solved

Last entry in a filtered selection

Hi Everybody, i cant find a solution to a query that i have to do for the report of my company.

 

The data is based on a excel file where some formulas and the date is already entered in advance. my colleagues only enter some values day by day. 

 

I now need to know the last entered value of column that has been filtered by a slicer.

For example this table:

 

If i set the slicer to 2.9.2020 to 4.9.2020 and use LASTNONBLANK the result for Errors Found is 10 which is obviously not correct. So i have read about the Index function but as you can see, the MAX Index is 4 which is also not the correct value.

 

Therefore i need a formula which obeys the Date Slicer and shows the Value "3" which in this example ist the true last value in that table.

 

I cannot delete the additional lines at the end because some of my colleagues might forget to copy the formulas or change them by accident.

 

Thank you in advance.

 

IndexDateErrors FoundErrors FixedWorking Hours
11.9.202022

100

22.9.20201010200
33.9.202032300
44.9.2020   
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, timelez 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Result = 
CALCULATE(
    MAX('Table'[Index]),
    FILTER(
        ALLSELECTED('Table'),
        NOT(ISBLANK('Table'[Errors Found]))
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    timelez - If I am reading this correctly:

    Measure = MAXX(FILTER('Table',NOT(ISBLANK([Errors Found]))),[Index])
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, timelez 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Result = 
CALCULATE(
    MAX('Table'[Index]),
    FILTER(
        ALLSELECTED('Table'),
        NOT(ISBLANK('Table'[Errors Found]))
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.