Still can't figure out level of detail for max date

[Samcooke]

I am still struggling with the Power BI Version of level of details so I am hoping for some help. I have the below data set where I have instrument kits that have gone out to surgery on 4 different dates. I only want to display the most recent date (10/8/2021). So in the below example there would be a single row showing Branch_Id, Kit_Instance_Id and Case_Surgery_Date

Data Set

When I try to write DAX for a new measure using the below, it does not work

Max Surgery Date = CALCULATE(MAX(TBL_CASE[Case_Surgery_Date]), ALLEXCEPT(TBL_KIT_INSTANCE, TBL_KIT_INSTANCE[Kit_Instance_Id]))

I get this where it is giving me the same date of each row

What am I doing wrong? Should I not be using DAX for this?

[v-shex-msft · Accepted solution]

Hi @Samcooke,

Perhaps you can try to use the following measure formula if helps:

Max Surgery Date =
CALCULATE (
    MAX ( TBL_CASE[Case_Surgery_Date] ),
    ALLSELECTED ( TBL_KIT_INSTANCE ),
    VALUES ( TBL_KIT_INSTANCE[Kit_Instance_Id] )
)

If you are not so clear about row and filter context, you can also take a look at the following blog:

Row Context and Filter Context in DAX - SQLBI

Regards,

Xiaoxin Sheng

[lbendlin]

might be a simple issue with the data model.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523