Forum Discussion

bsheffer's avatar
bsheffer
Continued Contributor
4 years ago

drillthrough filter breaking relationship

I have a drillthrough page that uses a single total measure to activate.

 

_churn NC hard close = calculate('Fact Cubes'[_a_total_net_contribution_sv churn], filter('Fact Cubes',isblank(related('Dim Mid Properties DateClosed'[DATE_CLOSED]))=FALSE()))
 
the activating page has a waterfall chart which uses this measure.  below the waterfall chart is a matrix with a lot of measures and a time series of year and month on the date table linked to the Fact Cubes table which the hard close measure also uses.  Therefore when I click on a month in the matrix the waterfall chart is implicitly filtered.
 
When I drillthrough to my page using that hard close measure after clicking on a month, this shows up in my drillthrough filters:

this filters my fact cubes table as expected.  Normally I would expect to see some kind of page filter to show up but that doesn't happen.  

 

the weird part is my fact cube table has a single direction relationship with my customer table  ( fact cubes <-- Dim View MID Properties).  If I then try to pull in a treeview tooltips from that customer table like merchant_name it shows a blank, which is  incorrect.  If I remove that year and month filter from the drillthrough (the highlighted part) by clicking that X then the merchant_name shows up.  The treeview is displaying the hard close measure values for all the merchants from the fact cubes table.

 

the only solution I've found is to use a measure to pull that value but ignore the date table filter:

_merchant_name =

calculate(min('dim view_mid_properties'[crm_name])
,all('Dim Calendar ActivityMonth')
)
 
I have never had to do this before.  It was always a simple drag and drop.
 
any ideas of why this is happening?
 
to summarize:
an implicit filter from my calendar table  when I drill through is breaking the relationship between my financial table and my customer table.  I can still get values from my customer table but I have to use a measure with an ALL(calendar table) in the calculate.

 

Need Help