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/spreadsheets/d/1guuFBOtL0HZ9V_nd3Ml7ww0I4AYh21Rd/edit?usp=sharing&ouid=110181330375607658941&rtpof=true&sd=true 

 

Please let me know if you need frther details

 

Regards.

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.

7 Replies

  • 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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    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.