Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Undo filter in Drillthrough

Hey guys,   so I created this drillthrough system:     And now, in the DrillDown of a certain Project, I want to be able to compare it to other projects (which are obviously filtered out)....
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    you could try this way as below:

    Step1:

    Create a separate project table and create a relationship as below:

    Note: The cross filter direction must be Single and is separate project (1:*) ProjectData

    Separate Project = VALUES('ProjectData'[Project])

     

    Step2:

    Create measure like this:

    Average Margin = SUMX(ALL(ProjectData), ProjectData[Margin])
    Basic Margin = CALCULATE(SUM(ProjectData[Margin]),FILTER(ALL(ProjectData),ProjectData[Project]=MAX('Separate Project'[Project])))

    And drag them into a vsiual with project field from the separate project table

     

    Result:

    Simple example, I drillthrough by USA

     

     

    and here is my sample pbix file, please try it.

     

    Regards,

    Lin

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous ,  Can you please explain with an example.

 

One of the way to remove the filter from all drill down below keep all filters

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-cross-report-drill-through#set-up-a-cross-report-drillthrough-target

 

In the new release, you have an option for conditional drill down

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-2020-feature-summary/#_Cond_dest_drill



Appreciate your Kudos.