Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
slackerhx
Helper II
Helper II

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. 

 

 

RankBuilding NameCountry Building IDUseGFAMonthly Rent
1ABC Building USAUS-NY-08Office500080,000
2CFG TowerJapanJP-NGY-02Industrial 2000020,000,000
3XYZ PlazaAustraliaAU-SYD-03Office300060,000

 

Appreciate anyone's help. 

 

Thank you. 

1 ACCEPTED SOLUTION
slackerhx
Helper II
Helper II

i think i found the solution to my question in this youtube video: 

 

https://www.youtube.com/watch?v=SsZseKOgrWQ

View solution in original post

3 REPLIES 3
slackerhx
Helper II
Helper II

i think i found the solution to my question in this youtube video: 

 

https://www.youtube.com/watch?v=SsZseKOgrWQ

v-jiascu-msft
Employee
Employee

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

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.