Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Max Value with condition in direct mode

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/spr...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , 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])

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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

     

    Meausre can

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Appreciate your help.. tried multiple ways but dint work

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Could you help me with logic to create a calculated column