Drill down Card visual shows all lines matrix instead of only the relevant lines with value 1 (0/1)

[Rashidb84]

Hi guys,

I've got a matrix with 15K lines.

I've got a calculated column (Column:

PO Remaining Value ZERO No Move 90 Days) showing either a value of 0 or 1. This to give me a total for the amount of relevant lines. 

On the matrix tab I added the column: PO Remaining Value ZERO No Move 90 Days

in the 'Add drill-through fields section. This gave me the option to drill through when hovering over the button on the print screen.

When PowerBI takes me to the Matrix tab it doesn't filter on the 11K lines but just gives me the total 15K lines.

Previously I tried to achive this with a Measure which didn;t work. I thought let me try to achive this now with a Calculated column. Not sure why this is not working either. 

Is it because the cardvisual is only adding up the values of column 

PO Remaining Value ZERO No Move 90 Days and not really filtering it? therefore the drill down just gives all lines (15k).

Is there an option without having to create a seperate button? 

Ideally I just want to click on the Card and drill through (tried to figure out if I could a filter on the 1s but not found any option).

[Rashidb84 · Accepted solution]

Hi All,

I came up with a solution. When following the steps described in my previous post I forgot to put the lock (yellow marked) while adding a filter to the Card visual.

Now I have locked the filter I can drill down to the underlying tab PO register and get the 17 lines as a result.

When I click on the RESET button on the PO register tab the filter on the matrix is removed. I get the 25 lines again.

When I come back on the KPI tab I can drill down again and get the expected 17 lines. This because as said the filter is locked. This is what I was looking for. 

Hope this was clear and helps out others as well with the same query.

[Anonymous]

Hi, @Rashidb84 

Can you share how your DAX is written, or can you provide a sample pbix or data that doesn't contain private data? Under normal circumstances, a measure can be recalculated based on a drill point. I created a sample as shown in the image below:

countrows = COUNTROWS('Sheet1')

Drill results:

You can check that your drillthrough settings are correct to make sure that the fields that need to be drilled are filtered based on columns. Also check your filtering context to make sure the filter is applied correctly. If you drill through or are there 15K rows, your filter may not work.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Rashidb84]

Hi V-jianpeng-msft,

I've created a test file with 25 lines.

Created the card via two different methods marked in yellow (KPI tab):

Formula measure:

PO Remaining Value ZERO No Move 90 Days = calculate(count(ME2N_in_YSPOcombined[Purch.Doc.Item]),filter(ME2N_in_YSPOcombined,TODAY()-ME2N_in_YSPOcombined[Last move]>=90 && ME2N_in_YSPOcombined[Remaining value]<0.001 && ME2N_in_YSPOcombined[Remaining value]>-0.001))

Formula for added Column in table:

PO Remaining Value ZERO No Move 90 DaysC = if(TODAY()-ME2N_in_YSPOcombined[Last move]>=90 && ME2N_in_YSPOcombined[Remaining value]<0.001 && ME2N_in_YSPOcombined[Remaining value]>-0.001,1,0)

PREVIEW

The output for both is correct 17 of the 25 lines contain the value 1.

To be able to drill through and get the relevant 17 lines, I added both the measure and added Column to 

the Drill through option on the PO register tab (as a test):

The drill through field on the KPI tab is empty as should be (my understanding) 

After adding these I get the Drill through option for both cards.

 

 When I select PO register, the action takes me to the PO register page but shows all data rather than the 17 lines that I had expected.

How can I sort this?

+ how can I remove the filter on the PO register tab to get all the data visible again?

Also maybe interesting to show that no filters are applied on the KPI tab:

I know that I can for example add column PO Remaining Value ZERO No Move 90 DaysC in the filter section and then filter on 1. When drilling down this will give me the 17 items. But When I have copied the details I'm not sure how to get the full list back. I created a bookmark to reset the table which doesn't work. If I manually remove the yellow marked option the full data appears (filter appplied due to drill down). But I want this to be removed via the button rather then me having to do this.

Hope this gives a clear picture of the issue. I created a test PBI file just to realize that I couldn't upload it.

[Anonymous]

Hi, @Rashidb84 

Thank you very much for your reply. I think you've created a calculated column, and then placed that settlement column in a card visual. You also create a measure that counts the number of rows in your filter. Then you drill through based on your calculated columns. I created a similar sample:

My calculated column calculations are as follows:

My measure is as follows:

page1 is set as follows:

page2 is set as follows:

The drillthrough field is set to a calculated column. Use the current calculated column and measure in the table visual. When no calculated columns are placed in the table visual, drilling through page1 will not give correct results:

If you don't want to place the calculated column in the table visual, then you can do it as in the screenshot below:

The correct thing to do about bookmark is to create two visuals that are the same size and all fields, and place them on top of each other. Use the following selection with bookmark to control the visible and hidden state:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Rashidb84]

Hi v-jianpeng-msft,

Thank you very much for your feedback.

I had tried to get this sorted via the Bookmark option but then I get the following problem:

If I add a filter on the KPI screen where the card visuals are shown (to only show the lines that contain 1):

1. for card visual that is created using the calculated column

2. for card visual that is created using measure

For both applies that I will only see 17 lines on the matrix page as expected. This is good. To get the full matrix table after showing the 17 lines I have created a bookmark as advised by you:

The problem now is that if I click on the bookmark I will see the full 25 lines as expected. This is good. But if I go back to the KPI screen I notice that the added filter to show only the lines with 1 has been removed. If I click on the drill through option I get the full 25 lines in stead of the 17 lines.

How can I make sure that clicking the CLEAR button won't remove the filter for the card visual. So each time I click on the Card visual I should see the result for the card visual and each time I click on the CLEAR buton I should see all lines.

To apply this to your example:

If you click on the card visual on your side (calculated column or measure) on page two where you see the matrix you should only see lines:

ID 3, 4, 5.

When you click on the CLEAR ALL SLICERS --> you should see all lines in matrix.

If you go back to the card visual and click on it you should again see lines: ID 3, 4,5 only. etc.

Not sure if this is how it works on your side?

if so, is it possible to share the file so I can analyse it?

Have been trying this for a while but don't fully understand this concept. Would appreciate it if you can explain this bit step by step?

To clarify I indeed don't want to show either the calculated column or the measure in the matrix. This is not relevant data for the users.  

For the benefit of other users I have sorted this issue before by adding a bookmark reference to the Matrix on the KPI tab via a seperate button. But this is not elegant. That's why I want this to work via the Card visual drill through option.