Forum Discussion

akoita92's avatar
akoita92
Frequent Visitor
4 years ago
Solved

Get last value for each id

Hello everyone
I explain my problem (which has been bothering me for a while) below.
I thank you in advance.

let's say I have two tables.

                           

 

  i have a slicer date filter, which comes from the second "Calendar" table

 

                   

 

 

What I want is to display the last value even if I filter. even if i filter on mars i want the other two rows to appear.

 

 

any answer will be appreciated, thank you.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi akoita92 ,

    According to your description, here's my solution.

    1.Create a measure.

    Check =
IF (
    MAX ( 'Table'[date] )
        = MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) ),
            'Table'[date]
        ),
    1,
    0
)

    Put the measure in the visual filter and let its value to 1.

    2.If one visual is not affected by another visual, one method is to not establish a relationship between the two tables, and another method is to cancel the interactions between the two visuals. Obviously the first method is not suitable for you. So first select the calendar slicer, then click the Edit interactions button under the Format ribbon.

    In the upper right corner of the table visual, select the None button, then uncheck the Edit interactions button.

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    akoita92 , In visual use this for values

     

    Measure =
    VAR __id = MAX ('Table'[ID] )
    VAR __date = CALCULATE ( MAX('Table'[date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
    return
    CALCULATE ( sum ('Table'[value] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[date] = __date )

     

     

    Table Max of Date and not summarized  id column

    • akoita92's avatar
      akoita92
      Frequent Visitor
      4 years ago

      Thanks a lot for your answer amitchandak 


      The mesure works fine and returns the last value for each ID.



      However, I have a relationship with the calendar table like this : 

       

       

      if I filter on March 2021, I get the following result

       

       

      What I want is that, even if I filter on March 2022, the lines for ids 2 and 3 appear (always with the value associated with the last date). Below is the caputure of the result I expect.

       

       

       

      is it possible ?

       

       

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi akoita92 ,

    According to your description, here's my solution.

    1.Create a measure.

    Check =
IF (
    MAX ( 'Table'[date] )
        = MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) ),
            'Table'[date]
        ),
    1,
    0
)

    Put the measure in the visual filter and let its value to 1.

    2.If one visual is not affected by another visual, one method is to not establish a relationship between the two tables, and another method is to cancel the interactions between the two visuals. Obviously the first method is not suitable for you. So first select the calendar slicer, then click the Edit interactions button under the Format ribbon.

    In the upper right corner of the table visual, select the None button, then uncheck the Edit interactions button.

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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