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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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