drill through action from dim 01 to fact to dim 02

[GACL]

In my PBI data model, there is a 1:N relationship
- between the dimension "project" and the fact "time registration" - based on "project code"
- between the dimension "Team" and the fact "time registration (hours)" - based on "Team ID"
- between the dimension "project" and the fact "budget (hours)" - based on "project code"
- between the dimension "Date" and the fact "time registration (hours)" - based on "Date"

One of the report sheets is called "Project Details"; it contains a Table visualization (sliced by the Date attribute "Year") with the following measures & attributes:
- MSR_01: the total hours in terms of time registration
- MSR_02: the total hours in terms of budget hours
- MSR_03: the (%)budget hours already consumed
- per project code (and only project related attributes)
- a measure that tells you how many teams are linked to the project code (by applying a cross filter "both" between the "Team" and the "time registration")

Now, I'm interested in the time registration details + the team details. For this reason, I've defined a drill through page.
Action A: selecting the MSR_01 in the "Project Details" -> right click + drill through -> jumping to the drill through page (which contains a mix of time registration + project fields & measures)
Action A results: performance issues - the details are not visible

Action B: define a slicer "Team" in the drill through page + selecting the MSR_01 in the "Project Details" -> right click + drill through -> jumping to the drill through page + selecting one of the Team values that become available in the slicer

Action B results:  the details are visible now in the Table - related to the selected (slicer) Team

Conclusion:
- in a first step, the "time registration" is filtered based on the "project code". Apparently, this is not enough in order to get the details visualized.

- I've defined a measure (which is applied as a filter on the Table visual in the drill through page) - generating the appropriate "Team ID" values based on the project code mentioned in the "Project Details".
Apparently, this is not enough in order to get all the details visualized.

- regarding Action B, the details become visible in the Table, only after selecting one of the Team values.

(The query load is too high - in case of using only the project code and the date)

How to integrate that additional "Team" filter...?

Thank you!

[v-echaithra]

Hi @GACL ,

Thank you for confirming that issue has been resolved. If further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[GACL · Accepted solution]

I implemented the following solution for this drill through scenario:
- define in the fact "time registration (hours)" the calculated column "CC_Related_TeamCode" - applying the RELATED function on the Dim attribute "Team.[TeamCode]"
- replace the Dim attribute "Team.[TeamCode]" in the Table visual in the drill through page by this calculated column "CC_Related_TeamCode"

This way, in the drill through page, the "time registration" is only filtered based on the "project code" and the "Year"; no need for extra visual filters.
The price I pay is the calculated column defined in the fact table (which contains two million records).
In the current situation, the drill through actions work smoothly and the impact on the data refresh (import mode) is "nil".

[amitchandak]

@GACL , based on what I got so far. 
Time registration is joined with the team. So on the second page (after drill), if the visual has team, then it should drill unless we switch off - using the Keep all filter 

[GACL]

Thanks for the feedback. The only differentiators in the the "Drill through - Keep all filters" list are the project code and the Year. As soon as you add a measure to this list, the "keep all filters" option will be greyed-out. Adding a measure - indicating the number of relevant time registration records or relevant team records - to the table visual in the drill through page is not enough to get the details available. Thank you.

[amitchandak]

@GACL , we had a similar issue. Do not add measures to the list unless most needed. Adding measure will help from drilling it from measure, but that means all filter will pass. 

One workaround that I have done is by duplicating the table of the field to not to pass the value
This was done for drill across
Power BI Partial Interactions, Selectively pass filter across visuals - https://youtu.be/SFEKQEic_sk

What is Drill Through in Power BI? How to selectively pass field values to drill through page?

https://www.youtube.com/watch?v=Yk8xWssAbdc&list=PLPaNVDMhUXGZ5uQZ_v0tfC5lIjEHoa6Yk

 Power BI Drill Through - More than one level: https://www.youtube.com/watch?v=hCAAvpppQmk
Power BI Drill Through when Userelationship is used: https://youtu.be/1BM_WGATrQA