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 ,

You can create this measure, put it in the visual filter and set its value as 1:

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

Attached a 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

v-yingjl Not to sure if you can assist further, your solution works exactly as I asked, but I have noticed that there are 2 rows that dont have invoice dates and therefore flagged as "0" and therefore not displayed in the table.  I need these rows to be displayed as well.  Is this possible?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

    • StuartSmith's avatar
      StuartSmith
      Power Participant
      5 years ago

      Ashish_Mathur Thanks.   I will take a look at your solution.

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

    Hi StuartSmith ,

    If you want to show the other rows which measure value is 0, just clear the visual filter.

     

    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

      v-yingjl sorry, i mean aswell as seeing the "Max" date for each country by filtering "1", I also need to see rows with no date, so, maybe something like setting "Max" = 1, "Blank" ="2". Hope thats clearer.

       

      • 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.