How to retrieve records in a matrix by filtering for latest date within a Direct Query source Table

[andra2]

Hello, 

I have a source table with bellow data that is using Dataverse Direct Query:

User       entrydate        team       strategy    stock

a	1/19/2022	team1	1	2
b	1/18/2022	team 1	1	2
c	1/17/2022	team1	1	2
d	1/20/2022	team1	2	2
e	1/18/2022	team 1	2	2
f	1/17/2022	team1	2	2

I want to display in a matrix table the below result. Basically, to retrieve the coresponding record of the latest day within each team + strategy + stock combination. 

User      entrydate          team       strategy  stock

a	1/19/2022	team1	1	2
d	1/20/2022	team1	2	2

The issue I am encountering is related to the Direct Query Mode (I cannot create calculated columns using max like filter etc) and this mode is being required by the client 

Please support me on this. 

Thank you very much!

[parry2k]

@andra2 glad to help. do subscribe to my youtube channel (link below) for interesting power bi how to's and tips/tricks. Cheers!!

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@andra2 here is the measure that you can use:

MM = 
CALCULATE ( 
    MAX ( Team[User] ),
    KEEPFILTERS (
        TOPN (
            1,
            ALLEXCEPT ( TEam, Team[Stock], Team[Strategy], Team[Team] ),

            CALCULATE ( MAX ( Team[Date] ) ),
            DESC
        )
    )
)

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[andra2]

Thank you very much @parry2k