Hi There
In the table (MY TABLE) below its very easy to create highest value but it is possible to create a slice with the top 10 price (Column F) ? Column A is the key
Expected Output
Your slice filter expression for the top 10 items with asset values can be something like
IN([REGISTRATION ID], TOP(ORDERBY(Table_Name[REGISTRATION ID], [ASSET VALUE], TRUE),10))
Please replace the “Table_Name” with actual table name. The expression assumes that [RGESTRATION ID] is the key column of the table. If not please replace suitably.
What if I were to re-condition with : Column G [SCRAP CODE] and Colum H [STOCKTAKE CODE] where SCRAP CODE = 6 need to be filtered and STOCKTAKE CODE =0 need to be filtered. How to nest that condition to the expression?
The possible value of SCRAP CODE IS 0, 6, 6.1, 6.2, 6.3 & 6.4 and the possible value of STOCTAKE CODE is 0, 12, 12.1, 12.2, 12.3, 12.4
Your new additional condition is not clear. You mentioned filter values as 6and 0. But thereafter you have given a list of multiple values.
It is not clear if you want to include all those filter values in the slice of exclude those? Could you clarify.
I am sorry for confusing you. Let me rephrase. In the [SCRAP CODE] column all other possible values are acceptable except the value 6 on that row has to be filtered regardless of any value in [STOCTAKE CODE] column at that instant. In [STOCKTAKE CODE] all other values are acceptable except the value 0 on that row has to be filtered regardless of what value is at [SCRAP CODE] column. To put this in summary below
[SCRAP CODE] [STOCKTAKE CODE] CONDITION
6 12, 12.1, 12.2 & 12.3 FILTERED ROW
0, 6.1, 6.2, 6.3 & 6.4 0 FILTERED ROW
6 0 FILTERED ROW
Thank you but are those rows to be filtered in ( included ) or filtered out (excluded) from the slice?
Those rows to be filtered out (excluded) from the slice.
Please try
IN([REGISTRATION ID], TOP(ORDERBY(SELECT(Table_Name[REGISTRATION ID] AND( [SCRAP CODE]<>6, [STOCKTAKE CODE ]<>12 )) , [ASSET VALUE], TRUE),10))
Yes, sorry, my bad. It is just a typo ,
Please try
IN([REGISTRATION ID], TOP(ORDERBY(SELECT(Table_Name[REGISTRATION ID] AND( [SCRAP CODE]<>6, [STOCKTAKE CODE ]<>0 )) , [ASSET VALUE], TRUE),10))
2 Likes
Wow thank you so much. You are great ! It shows exactly 10 rows from all the IDs I show below. I compare manually row by row & ID by ID they are all correct. Just that the slice $ value is not in descending order but they are all there. How nice if that can be arrange in Descending $ value order. But it’s ok this is good enough if this is not possible.
This is the final expression
IN([ASSET ID],TOP(ORDERBY(SELECT(Asset Mgmt Database[ASSET ID],AND([SCRAP CODE]<>6,[STOCKTAKE CODE]<>0)),[ASSET VALUE], TRUE),10))
It’s ok I put in a View and use the feature Sort By. Resolved.
1 Like
You are welcome. Good to know that the solution worked.
As for ordering those by the descending order, you can sort the view by the $ value column. Alternatively the user can simply sort the table view by the column. The view needs to be based on the slice.
Edit: I saw your post regarding sorting in the view after I sent this post. Great- yes, sorting it in the view is correct approach.
1 Like