MAX value in direct query mode

[Zuzana]

Hi, 

I'm really struggling to find solution for a longer time now. I went through all the advices here, but non of them is working to me. 

I'm working in Direct query mode and found some solutions to this already, but nothing is working as I need. 

I have one measure already, which is still not showing the highest category number in every row per child ID: 

#Max Cat = 

VAR ctl = SELECTEDVALUE([child ID])
VAR dupl = FILTER(ALLSELECTED([table]),[child ID]=ctl)

RETURN
MAXX(table, table[category number] )

I'd need the table look like this: 

[DataNinja777 · Accepted solution]

Hi @Zuzana ,

In Direct Query mode, if you're trying to show the maximum category number for each child ID across all related parent ID rows, your current DAX measure isn't correctly removing the row-level filter context. To achieve the desired result—where each row displays the same maximum category number per child ID—you should revise the measure to explicitly remove filters on everything except the child ID. You can use the ALLEXCEPT function to achieve this. Here's the corrected measure:

#Max Cat =
CALCULATE(
    MAX('your_table'[category number]),
    ALLEXCEPT('your_table', 'your_table'[child ID])
)

This version ensures that the measure returns the same maximum value of category number for all rows sharing the same child ID, regardless of other filters in the table. Just replace 'your_table' with the actual name of your table. This approach works in Direct Query mode and avoids the pitfalls of SELECTEDVALUE not resolving correctly due to row context limitations.

Best regards,

[Zuzana]

Hi @DataNinja777 ,

Thank you very much for your solution. This seems to be working well. 🙂 

You saved me! 
Have a wonderful weekend.