Rank Display only top 10 and add a filter to rank formula

[Sea_and_Anne]

Hello,

I have one table with the Machine name and the number of Alarms. I want to create a simple graph with the top 10 of the machines with the hightest number of alarms, but witha  condiction they should be lower than 14.
So, i have created the column rankAlarm and then i have filtered my graph with this column <10. However i have so many records with the same number of alarms..i.e in the same rank level...do you know how can i only display only 10 machines? And do you know how can i do this rank with the filter Alarms < 14?

Thank you in advance for your help

🙂

[v-yulgu-msft]

@Sea_and_Anne

Hi Sea_and_Anne,

You can create a new table which contains only the first 10 rows and each Alarm value is less than 14.

NewTable = TOPN(10,FILTER('Table','Table'[Alarm]<14),Table[Alarm],DESC)

Thanks,
Yuliana Gu

[Sea_and_Anne]

Hello Yuliana_GU,
Thanks for your help!
I have other doubt, if I have more than one register per machine, as the follow case, where i have Machine A with 4 registers. How can I write the TOPN function in order to appear machine A only one time and not fill the first 4 levels of the top 10.

NewTable = TOPN(10,'Table','Table'[Alarm],DESC)

Machine	Alarms	Nature
A	14	a
A	14	s
A	14	f
A	14	g
B	13	a
B	13	g
D	12	r
E	11	t
F	10	h
G	6	w
H	5	q
I	5	a
J	4	g
K	3	h
L	2	t

[Greg_Deckler]

Seems like you need an additional piece of information. Perhaps adding an ID to your Machine table, 1, 2, 3, 4, 5 and then adding this number to your Rank would get you where you need to be.

[Sea_and_Anne]

I thought about this, how can i add the ID on power Bi?

[BhaveshPatel]

Hi There,

To add a ID Column, Go to the Query Stage, Under Add Column Tab, Select Add Index Column and You are done.