Measure Works in Card but won't correctly filter my table when I Drill through to Matrix

[Angel2022]

I have a measure that calculates and counts all end dates in a field which are less than the current month end, on a card visual: 

Dates thru Current Month = COUNTROWS(FILTER('Excel Table',NOT(ISBLANK([Client]))&&[End Date]<=EOMONTH(TODAY(),0)))

I placed the above measure in the Drill through section of a table visualization on another tab.  When I right click on the card to drillthrough however, it takes me to the matrix but doesn't filter the matrix rows correctly. It doesn't filter it at all actually.  Can someone please help me to rewrite this measure to allow drillthrough on my table?  The measure works great because I don't have to manually change the dates on the visual filter every month. I just need to also be able to drillthrough on it. 

Thanks!

[v-chenwuz-msft]

Hi @Angel2022 ,

There is a possibility that happened while I was testing. The null value is "", it is displayed as blank, but it is still included in the calculation. So it can be changed to the following code.

Dates thru Current Month =
// COUNTROWS(FILTER('Excel Table',NOT(ISBLANK([Client]))&&[End Date]<=EOMONTH(TODAY(),0)))
COUNTROWS(
    FILTER(
        'Excel Table',
        [Client] <> BLANK()
            && [End Date] <= EOMONTH( TODAY(), 0 )
    )
)

You can try it and if no works, please share your pbix file without sensitive data.

Best Regards

Community Support Team _ chenwu zhu

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

[Angel2022]

Hi, all of the measures worked correctly on the Summary Tab for the card visuals, but my issue is with 2 measures which won't filter the table on the Detail tab when I right-click the Card Visual to drillthru.  In other words, currently, they will drill through to the table, but don't correctly filter the number of records as seen on the Card Visual.  For your reference, you'll see on the Summary Tab that the "Begins This Month" Card Visual with 38 records drills through and filters the correct number of records in the table on the Detail tab; whereas the other 2 Card Visuals with different measures will drill-thru to the Detail tab, but don't filter the table at all.  Please see attached pbix file: Sample PBIX

[Kumail]

@Angel2022 

If these don't drill through to logic correctly, what would be the correct number for two cards?

Regards

Kumail Raza

[Angel2022]

Hi Kumali -

The correct number is on the cards (62 and 100), the measure is working. The problem, as I mentioned is that when you right click on them to drill-thru to the Detail tab, it doesn't filter the table correctly for the 62 and 100. I hope that makes sense. 

[Kumail]

@Angel2022 

Is this correct? 

62, 100 as total at the end of the details tab.

Regards

Kumail Raza

[Kumail]

@Angel2022 

Drill through takes it to this sheet.

Which column/value is incorrect?

Regards

Kumail Raza

[Angel2022]

No, it's the ID Count Column that's incorrect.  I need it to filter the table when you drill-through so it only shows 62 or 100 records -  similar to the Card Visual that's correct (Begins This Month), which correctly filters the table for only 38 records, which you can see in the ID count. 

[Kumail]

@Angel2022 

Passing of DAX filters in the drill through is not allowed.

You can use this work around.

https://community.powerbi.com/t5/Desktop/Passing-DAX-filter-conditions-to-drillthrough-report/m-p/73...

Regards

Kumail Raza

If this answers your query, mark it as solution.

Kudos are appreciated.

[Angel2022]

Thanks so much for your help, but the link you provided never actually resolved the issue on that thread.  I think there has to be a workaround solution, and per my original question, I'm wondering if someone else can help me write my my measure differently for my specific use case.   My first card is working with the number 38.

[Kumail · Accepted solution]

@Angel2022 

Attached find images and reference file for card 2 and card 3 visuals with drill through.

https://drive.google.com/file/d/1f7jBg7r3Ef06aOJi8s3gMqdf1BTTgo57/view?usp=sharing

Regards

Kumail Raza

[Angel2022]

Works Perfectly! Thank you so much!!!

[Kumail]

Hello @Angel2022  

If you could send a sample .pbix that demonstrates what you are looking to get. It would really help to provide you with a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards
Kumail Raza