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
jdbuchanan71
Super User
Super User

Top N and the rest with Drillthrough

Good afternoon,

I am trying to get drillthrough to work from a TopN and Remaining view (show the top 5 customers and group the rest under "Remaining").  The grouping and reporting is working fine thanks to these posts.

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/

But I cannot get the drillthrough to work.  This is because the table that is created to hold the list of things + other is not connected to any of the fact tables.  I don't think there is a way to get drillthrough to work with the disconnected table but I wanted to ask.

The linked model has a sales table and the topn table as well as topn customer used in the visuals.  It also has a drillthrough page set up for testing.

 

Sample PBIX file: https://www.dropbox.com/s/iaycve8l1ea9vxl/TopN%20and%20Drillthrough.pbix?dl=0

 

When I drillthrough on a customer with 3.3 M in sales I get the entire 15.3 M on the drillthrough:

drillthrough example.png

Anyone have any thoughts?

 

Thank you,

John

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @jdbuchanan71 ,

 

Since you have disconnected table when you make the drilltrough it's not filtering any value since they don't have a relationship.

 

Do the following:

  • Create an inactive relationship between Customer table and ctCustomers by the column description
  • Create the following measure to use on your drilltrough table:
Sales Measure =
CALCULATE (
    SUM ( Sales[Sales] );
    USERELATIONSHIP ( ctCustomers[Customer Description]; Customers[Customer Description] )
)
  • Add the measure to your drilltrough table and filter to only show non blank values

Result is below and attach PBIX file .

 

 

drilltrough.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @jdbuchanan71 ,

 

Since you have disconnected table when you make the drilltrough it's not filtering any value since they don't have a relationship.

 

Do the following:

  • Create an inactive relationship between Customer table and ctCustomers by the column description
  • Create the following measure to use on your drilltrough table:
Sales Measure =
CALCULATE (
    SUM ( Sales[Sales] );
    USERELATIONSHIP ( ctCustomers[Customer Description]; Customers[Customer Description] )
)
  • Add the measure to your drilltrough table and filter to only show non blank values

Result is below and attach PBIX file .

 

 

drilltrough.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you @MFelix for the answer and including both the .pbix and video!  It seems there are two kinds of things in DAX.  Those that can be done and those that you just don't know how to do yet.

Hi,

In DAX there are a lot of little things especially when you are referring to measure that are based on context so just changing a small detail it's enough to make or break a calculation.

On your case what I did was to setup the context of the customer that didn't existed with disconnected tables so not being abnlw to filter from main table to drill through, bu of you had an active relation your Top N would not work.

Workaround is to activate the relationship when you need it so on the detail table using the measure.

Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.