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

[klajmexka]

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

9874	98
9874	102
9874	58
9874	104
9874	228
25	11
25	12
941	88
941	92

and I'd like to get this output:

nmb   ID

9874	228
25	12
941	92

Thanks!

[V-lianl-msft · Accepted solution]

Hi @klajmexka ,

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.

[parry2k]

@klajmexka 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.⚡

[klajmexka]

any formula suggestion please?

[parry2k]

@klajmexka 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.⚡

[klajmexka]

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?