Forum Discussion

sasdfasdfsad's avatar
sasdfasdfsad
Icon for Advocate IV rankAdvocate IV
1 year ago

Matrix Drill through on specific cell/measure? (Intersection/value of row and column on matrix)

I have a matrix that summarizes employees by region and some demographic data (veteran, male, female, a few others).

I want to let my end users click on one of the cells in the region row (Veterans) and drill through to ONLY see the details for that region that are vets.  

However, the drill through only filters on the Region column, it doesn't pickup that I also want to apply the filture to return the same measure count I clicked on:

I want to go to a details page where there are only 2 rows and each are Veterans:

But instead of just the 2 rows of Veterans I get all 4 rows (all employees in the North region)

 

Desired (veterans were clicked so I only want to see the two rows that were Veterans)

Actual Result

 

 

How can I make a drill through so that it drills through on a measure?  

So that if I click on "Total Veterans" it only returns the two rows that are counted in the measure (have a value of 1 or TRUE).

 

If my user views employees by region and the user wants to drill in on a SPECIFIC column value (Veterans = True, or Male = True, etc...) how can I get my drill through details results to only show those?  Those values aren't columns in the summary matrix but measures.

My data table:  (Overly simplified for this example, I wouldn't normally store the data this way of course)

RegionEmployeeIDMaleFemaleVeteran
North5561 1
South5571 1
East5581  
North559 1 
East560 11
South5611  
North5621 1
West563 1 
North564 1 
East5651 1
West5661 1
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi sasdfasdfsad 

     

    As far as I know, the drilldown source has to be a column and not a measure. You can create calculated columns to achieve a similar effect.
    1. Create a calculated column in your table using 'True' and 'False' to identify each employee as a veteran or not.

    IsVeteran = IF([Veteran] = 1, TRUE(), FALSE())


    2. Drag the new calculated column into your Matrix visual, right-click on the intersection of the 'North' row and the 'True' column, and select Drillthrough.


    3. At this point the drill down page will apply both 'Region = North' and 'IsVeteran = True' filters.

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • sasdfasdfsad's avatar
      sasdfasdfsad
      Icon for Advocate IV rankAdvocate IV
      1 year ago

      Unfortunately, it appears this is a functional limitation of PowerBI.

      Adding the additional column on the top DOES allow it to work, but now we have doubled the number of columns and the user has to determine if they want to see counts that apply to Veteran or Non-Veteran

      Instead of being able to click on the TotalVeteran count without clarifying which subset it is in

       

       

       

       

      Not possible to drill in without splitting the matrix under a grouped column:

       

       

      Thank you for the help though, it looks like I just have to deal with this failing of Power BI compared to other BI tools.

    • sasdfasdfsad's avatar
      sasdfasdfsad
      Icon for Advocate IV rankAdvocate IV
      1 year ago

      Apologies, I don't have a way to upload it.  

      It is only that one data table though.