Forum Discussion

Vanditha's avatar
Vanditha
Microsoft Employee
2 years ago
Solved

Urgent !!! Query to Get latest status/value Using Dax/Power query

Hi  Every one . I need help on beow requirement.

Package rev needs to be sorted out in  desc. I did this. However main requirement is need to get the latest status based on the PackageRev column.

 

I have tried 

=
CALCULATE (
    MAX ( Merge1[PackageRev] ),
    FILTER (
        Merge1,
        Merge1[Application version id] = EARLIER ( Merge1[Application version id] )
    )
)
)

 

Measure2 = CALCULATE(
    VALUES(Merge1[Status]),
    ALLEXCEPT(Merge1, Merge1[Application version id]),
    FILTER(
        Merge1,
        'Merge1'[PackageRev] = CALCULATE(MAX(Merge1[Packagerev]), ALLEXCEPT(Merge1, 'Merge1'[Application version id])
    )
))
Column 2 = CALCULATE(MAX(Merge1[SortedColumn_new]), ALLEXCEPT(Merge1, Merge1[Application version id]))

 

but these queries not giving exact results.
Original data

Expected data

 

Data Modeling
Report Server
Reports
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Vanditha ,

     

    Based on your description, I obtained the following results using the sample you provided:

    Latest Rev = var _t1= ADDCOLUMNS('Table',"Test",MAXX(FILTER(ALL('Table'),[ID]=EARLIER([ID])),[PackageRev]))
var _t2 = ADDCOLUMNS(_t1,"True",IF([Test]=[PackageRev],1,0))
return CALCULATE(MAXX(FILTER(_t2,[True]=1),[PackageRev]))

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Vanditha ,

     

    Based on your description, I obtained the following results using the sample you provided:

    Latest Rev = var _t1= ADDCOLUMNS('Table',"Test",MAXX(FILTER(ALL('Table'),[ID]=EARLIER([ID])),[PackageRev]))
var _t2 = ADDCOLUMNS(_t1,"True",IF([Test]=[PackageRev],1,0))
return CALCULATE(MAXX(FILTER(_t2,[True]=1),[PackageRev]))

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

     

  • Vanditha's avatar
    Vanditha
    Microsoft Employee
    2 years ago

    Thanks for your support. I have achieved this formula in other way by creating group by with Key column. Thanks for your help and support.