Max Value with condition in direct mode

[evvxp13]

Hi,

've attaced a smple report and need to fetch the status of the max Date under each Cat and under each ID.

We are using Direct query mode to get this query.

https://docs.google.com/spreadsheets/d/1guuFBOtL0HZ9V_nd3Ml7ww0I4AYh21Rd/edit?usp=sharing&ouid=11018... 

Please let me know if you need frther details

Regards.

[v-yalanwu-msft · Accepted solution]

Hi, @evvxp13 ;

It is not recommended to create a column in DQ mode because there are many restrictions. You are advised to create a measure as follows:

Measure = CALCULATE(MAX('Table_6'[Status]),FILTER(ALL('Table_6'),[ID]=MAX('Table_6'[ID])&&[CAT]=MAX('Table_6'[CAT])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@evvxp13 , Try a measure like

measure =
var _max = maxx(filter(allselected(Table), Table[ID] = max(Table[ID]) && Table[status] = Max(Table[Status])), Table[Date])
return
maxx(filter(allselected(Table), Table[ID] = max(Table[ID]) && Table[status] = Max(Table[Status]) && Table[Date =_max), Table[Status])

[evvxp13]

Hi,

Actually i need a new calculated column or measure beside status as master status and the logic should work there?

Please advise.

REgards

[amitchandak]

@evvxp13 , I doubt this kind of logic can work for calculated columns in case of a direct query.

Meausre can

[evvxp13]

Could you help me with logic to create a calculated column

[evvxp13]

 Could be calcuated column or measure but as per the dataset i've attached, need to capture the final status based on above conditions at row level.

Regards

[evvxp13]

Appreciate your help.. tried multiple ways but dint work