Forum Discussion
Top 10 with filter
Hello all,
I have one table with the Machine name , the number of Alarms and the alarm nature. I want to create a simple graph with the top 10 of the machines with the hightest number of alarms.
But some have in name the word "error" , so i want to filter in order to show only the machines that don't contain this word.
So, i have created the top 10 tab. But isn't filter the name...do you know the reason?
NewTable = Calculate(TOPN(10,SUMMARIZE(SampleTable,SampleTable[Machine],"TotalAlarms",SUM(SampleTable[Alarms]) )); SampleTable[Machine] <> "% error % ")
| Machine | Alarms | Nature |
| A | 14 | a |
| A | 14 | s |
| A | 14 | f |
| A | 14 | g |
| B | 13 | a |
| B error | 13 | g |
| D | 12 | r |
| E | 11 | t |
| F error | 10 | h |
| G | 6 | w |
| H | 5 | q |
| I error | 5 | a |
| J | 4 | g |
| K | 3 | h |
| L | 2 | t |
Thanks in advance for your help
Hi, Sea_and_Anne
You can also achieve that in DAX, please refer to the below formula:
NewTable = TOPN(10,SUMMARIZE(FILTER(SampleTable,FIND("error",SampleTable[Machine],1,0)=0),SampleTable[Machine],"TotalAlarms",SUM(SampleTable[Alarms]) ))Best regards,
Yuliana Gu
2 Replies
Hi, Sea_and_Anne
You can also achieve that in DAX, please refer to the below formula:
NewTable = TOPN(10,SUMMARIZE(FILTER(SampleTable,FIND("error",SampleTable[Machine],1,0)=0),SampleTable[Machine],"TotalAlarms",SUM(SampleTable[Alarms]) ))Best regards,
Yuliana GuSea_and_Anne : You can remove rows with Machine having "Error" word in Query Editor. Please screen shot.
Since you need only Machine and Alarm , remove Nature column . you will get the result.
