Forum Discussion
Filter table using TopN results
Hi All,
I've been combing the forums for a solution to an issue that i've encountered. I'm trying to populate a table of details using the TopN selector that i've created. I was successful in populating the numerical details using CALCULATE function but i'm not sure which DAX function should I use for text values.
For filtering of numerical details using TopN selector, i'm using the following measure:
Rental Savings Top =
If(HASONEVALUE('TopN'[TopN]),CALCULATE (
[Savings from rental analysis],
TOPN ( 'TopN'[TopN Selection], ALL( 'Masterlist of Leases'[Lease ID]),[Total Savings (USD)])),[Savings from rental analysis])
where [Savings from rental analysis] and [Total Savings (USD)] are calculated measures that contains numerical values and the ranking is based on [Total Savings (USD)].
My aim is to achieve the same for other text details such that the eventual output table will be as follows when a TopN value is selected. When no TopN value is selected, the table will display all rows of details.
| Rank | Building Name | Country | Building ID | Use | GFA | Monthly Rent |
| 1 | ABC Building | USA | US-NY-08 | Office | 5000 | 80,000 |
| 2 | CFG Tower | Japan | JP-NGY-02 | Industrial | 20000 | 20,000,000 |
| 3 | XYZ Plaza | Australia | AU-SYD-03 | Office | 3000 | 60,000 |
Appreciate anyone's help.
Thank you.
i think i found the solution to my question in this youtube video:
3 Replies
Hi slackerhx,
Could you please share a simple? You can upload it to OneDrive or Dropbox and post the link here. Please mask sensitive data before uploading.
Best Regards,
Dale
Hi Dale v-jiascu-msft,
Please see sample file link below:
https://1drv.ms/u/s!AoVBzNM7bDang2Uu_j-CNH2VlhZT
From the file, when you click on the 'TopN' filter the table recalculates subtotal for "Rental Savings". However, i not only want the table to recalculate the subtotals, i also want the TopN to act as a filter to pull out the TopN Lease ID.
Please let me know if further clarifications of my intent is required.
Thank you.
i think i found the solution to my question in this youtube video:
