cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slackerhx Regular Visitor
Regular Visitor

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

Accepted Solutions
slackerhx Regular Visitor
Regular Visitor

Re: Filter table using TopN results

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

 

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

3 REPLIES 3
v-jiascu-msft Super Contributor
Super Contributor

Re: Filter table using TopN results

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.
slackerhx Regular Visitor
Regular Visitor

Re: Filter table using TopN results

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. 

slackerhx Regular Visitor
Regular Visitor

Re: Filter table using TopN results

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

 

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 49 members 1,234 guests
Please welcome our newest community members: