Drillthrough filter

[sainig546]

Hi all,

i have a requirement where i need to show count of ids on the main page and than using  drill through its detailed data ,

the measure i am using to count ids shows current+last month data when all is selected on date slicer and it shows respective month data if something is selected on date slicer

main report pagemeasure for count by id\

but how can i implement the same thing on my drillthrough ,

This is the current output when all is selected on date slicer which is wrong since it should only show in this case january+december data for all selected

drillthrough output for all

[bcdobbs · Accepted solution]

See what you make of this:
Drill Through Example 

You were making life hard for yourself and slowing power bi down by converting your dates to text strings to join on. I think you may have something odd coming in from power query so go into it and make sure column types are set as Date not datetime.

I then improved your date table and was able to tidy your filter logic.

Finally there is then a visual filter set on the drill through table equal to 1

If you're unsure of anything I've done please reply and can help.

[AlexisOlson]

Instead of defining a new measure, I think you could get an equivalent result by setting the measure filter [Count Id] is not blank.

This won't make the drill-through show the prior month too (as OP wants) but adding [Count Id] to the detail visual could fix that (and remove the need to have it as a measure filter).

[sainig546]

sorry since i just made a sample file that why dates was still text there.and that visual filter on drill through actually has  a purpose .if you open that caluclated column you will see its giving a 1 or value based on created ts qnd updated ts and since that same filter was used in count measure it is needed in drillthrough as well. 

but still how can i create that measure which was the main question i had

[bcdobbs]

I'm sorry I'm not quite sure what you mean. Is the measure in the file

I sent not doing what you need?

[sainig546]

sorry actually i didnt found that file first . itsw working great and that date table you created is also really helpfull

[bcdobbs]

I'd be really interested if anyone has a better solution because I've hit this in the past.

I got round it by building a measure with the same logic baked in that returned either 1 or 0 depending on whether a row should be shown or not. Then used that measure as a visual level filter on the visual table on the drill through page. Think you could do something similar here.

[sainig546]

Hi bcdobbs,

i think it can work but how do we implement that here using calculatetable?

sorry i am still rather new to this so if you can with an example query that would be great

[bcdobbs]

Can you share a demo pbix file with same structure and I'll give it a go.

[bcdobbs]

Can you share a demo pbix file with same structure and I'll give it a go.

[sainig546]

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

here is the pbix link ,dont see an option to share file here

[bcdobbs]

Hi, just looking and trying to understand the original measure.
What should be being displayed when no month is selected? It just shows 1 when I do it on the demo file you sent.

[sainig546]

on the card it should show the count of id in last month + current month since there is only one value for december its showing one, in the drillthrough it should show all the detail for that id like created date ,updated date ,id