Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering highest ID based on one unique number in direct query mode

Hello,

 

I'm stuggling with a solution how to filter only highest ID for each number in table. I managed to do it in offline DB however not able to make it work in direct query mode.

 

Let's say we have table like this:

nmb   ID

987498
9874102
987458
9874104
9874228
2511
2512
94188
94192

 

and I'd like to get this output:

nmb   ID

9874228
2512
94192

 

Thanks!

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    Please try to create a measure like this and apply it to visual level filter.

    Measure 2 = 
var max_id = CALCULATE(MAX('Table'[ID]),ALLEXCEPT('Table','Table'[NMB]))
return IF(MAX('Table'[ID])=max_id,1,0)

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous create a mesure and in the table visual use nmb and following measure

     

    High =  MAX ( Table[Id] )

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      great, that is easier then I ever though 🙂 however I'd need one more thing. I'd like to add one additional column to this table which contains Notes, (string type) and only the Note with the highest ID should be visible. I thought it will do the job automatically when only nmb and highest ID is visible however it will show all IDs again. Don't you please know where could be the issue?

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous you have to write another measure to get the note based on the highest Id.

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      any formula suggestion please?

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Please try to create a measure like this and apply it to visual level filter.

    Measure 2 = 
var max_id = CALCULATE(MAX('Table'[ID]),ALLEXCEPT('Table','Table'[NMB]))
return IF(MAX('Table'[ID])=max_id,1,0)

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.