Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
5 years ago
Solved

Latest Date from Multiple Rows

I have a table visual similar to the below and need to filter the table, so it only shows the latest for each coumtry.     Unfiltered Table Country Date Qty UK 01/09/2020 10 UK 01/10...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi StuartSmith ,

    To make the card visual show the same total in the table visual, you can create another measure to calculate it and put it in the card visual:

    Sum of Qty = 
CALCULATE ( SUM ( 'Table'[Qty] ), FILTER ( ALL ( 'Table' ), [Control] = 1 ) )

    Check the modified sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

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

v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hi StuartSmith ,

Try to modify the measure like this:

Control = 
VAR _max =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Country] IN DISTINCT ( 'Table'[Country] ) )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Date] ) = _max || SELECTEDVALUE('Table'[Date]) = BLANK(), 1, 0 )

Basically if uses the previous measure, the blank date should also be displayed.

Check the below sample file.

 

Best Regards,
Yingjie Li

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

StuartSmith's avatar
StuartSmith
Power Participant
5 years ago

Thanks for your continued support (and patience). You additionaly solution worked, and now trying to get the filtered quantity value showing in a cardbut it shows the unfiltered quantity.  I therefore, thought I would be able to apply your measure to the card filter, but it doesnt allow me to use the dropdown options. 

 

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi StuartSmith ,

    To make the card visual show the same total in the table visual, you can create another measure to calculate it and put it in the card visual:

    Sum of Qty = 
CALCULATE ( SUM ( 'Table'[Qty] ), FILTER ( ALL ( 'Table' ), [Control] = 1 ) )

    Check the modified sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

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

    • StuartSmith's avatar
      StuartSmith
      Power Participant
      5 years ago

      Thanks, thats great and that also helped me with my calculated table question, and I created a calculated table replicating my invoice table, but only containing the most recent invoice rows.  Then I can refer to the calculated table. 

       

      Will the calculated "Invoice Table" automatically update with any changes made on the "Invoice Output" table when the "Invoice Output" table is refreshed.  I believe it does, but just checking.

       

      Invoice Table = CALCULATETABLE('Invoice Output', FILTER ( ALL ( 'Invoice Output' ), [Latest Invoice Date] = 1 ) )
       
      Once again, thanks for your continued support.
       
       
      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        You are welcome😀.

         

        Best Regards,

        Yingjie Li

  • StuartSmith's avatar
    StuartSmith
    Power Participant
    5 years ago

    Following on from my earlier post, instead of working with measures to get my data filtered, is there a way to create a calculated\filtered \dynamic table that would show the same result as your measure.  I could then simply refere to the table for cards, charts, etc.

     

    I hope that makes sence.