Drill through from dataset1 and filter dataset2 on selected value of drill through

[Anonymous]

Hello there,

I have a specific question related to Filter, Selected value, and calculate table which I couldn't find an answer online. If you can answer this that would be really appreciated.

Purpose: I want to drill through a field on dataset 1 and when it comes to the drill-through page, I want to display a table based on dataset2 but that should be filtered by the value that was passed through the drill.

What I need: I have created measure to get the selectedvalue from drill. and I was calculating table and applying filter the column based on measure. But somehow it is not filtering the table. Below is the DAX i am using. Please have a look and let me know where I am making mistake.

FilteredTable = CALCULATETABLE('Estimator_Pg2_Table2',FILTER('Estimator_Pg2_Table2','Estimator_Pg2_Table2'[EstimatorName] = SELECTEDVALUE(Estimator_Pg1_Btm2_Tbl[EstimatorName])))

explanation:
SELECTEDVALUE(Estimator_Pg1_Btm2_Tbl[EstimatorName]) = This is the measure created to get the selected value of estimator.
I want to filter the table Estimator_Pg2_Table2, where the estimator name is equal to whatever value measure has.

This is not giving any error but the table is showing only rows where the estimator name is null. instead of the value.

Your input is really appreciated.

Thank you
Rajan

[jdbuchanan71 · Accepted solution]

@Anonymous 

I would create a bridge table between Estimator_Pg1_Btm2_Tbl and Estimator_Pg2_Table2.  This bridge table will be based on the EstimatorName and can be created with this DAX:

Estimator_Bridge = DISTINCT (
Estimator_Pg1_Btm2_Tbl[EstimatorName] )

You join from Estimator_Pg1_Btm2_Tbl to Estimator_Bridge using the EstimatorName and make the join bi-directional.  Then you join from Estimator_Bridge to Estimator_Pg2_Table2 which should already be a 1:* be default.  This will allow filters to flow from the Estimator_Pg1_Btm2_Tbl up to Estimator_Bridge and back down to Estimator_Pg2_Table2 which will facilitate your drillthrough without having to try and calculate tables.

[jdbuchanan71 · Accepted solution]

@Anonymous 

I would create a bridge table between Estimator_Pg1_Btm2_Tbl and Estimator_Pg2_Table2.  This bridge table will be based on the EstimatorName and can be created with this DAX:

Estimator_Bridge = DISTINCT (
Estimator_Pg1_Btm2_Tbl[EstimatorName] )

You join from Estimator_Pg1_Btm2_Tbl to Estimator_Bridge using the EstimatorName and make the join bi-directional.  Then you join from Estimator_Bridge to Estimator_Pg2_Table2 which should already be a 1:* be default.  This will allow filters to flow from the Estimator_Pg1_Btm2_Tbl up to Estimator_Bridge and back down to Estimator_Pg2_Table2 which will facilitate your drillthrough without having to try and calculate tables.

[Anonymous]

I have applied that and working fine without row-level security. If I want to see the reports using the security it is giving the following error. 

Error Message:
Join paths are expected to form a tree, but the table 'Estimator_Pg2_Table2' has two join paths to table 'RowLevelSecurity_1of2': 'Estimator_Pg2_Table2'->'EstimatorBridge'->'Estimator_Pg1_Btm2_Tbl'->'RowLevelSecurity_1of2' and 'Estimator_Pg2_Table2'->'RowLevelSecurity_1of2'.

If I have researched and many of the user having same problem due to bidirectional filter. But I guess I need that for other solution. Can you please help me to resolve this as well.

[jdbuchanan71 · Accepted solution]

@Anonymous 

You don't really need your security tables linked into your model.  They are there so you can get a list of values based on the current user and apply them as fitlers.  Take a look at this discussion which is talking about the same topic.

https://community.powerbi.com/t5/Desktop/Dynamic-RLS-filtering-multiple-tables/m-p/1320568#M571726

If you are not able to get to a solution please post a new question as this thread has already been marked as solved.  Also, take a look at this blog post about items that will help us answer your questions.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thank you So much..!!!!

I have been searching for this solution for the past 24 hours and this has been a nightmare for me. You have resolved the problem.

Thank you so much again.

Really appreciated.