CODEWITHSUNDEEP
powerbidaxmeasuredrillthrough
Adrian lock
Adrian lock

Reputation: 3

Unable to put measure in drill through to pull correct rows with out table crashing

I have a very larger dataset with 30+ tables used for IT reporting purpose. I'm in the process of writing a KPI report which shows how we are doing around our Customer KPI. A large proportion of these relate to Tickets logged. This report has 6 tiles per page(see below image link example) and these show an overview of current month and line graph below the last 3 months. Plus target on the side.

KPI Tiles

This all works fine and below these are some further visuals normally 2 around the KPI. This part of the report works fine. KPI are calculated using a number of measure which basically shows current month KPI unless data is filtered then it pulls relevant month from correct calendar table.

KPI Number of Tickets for last available month = 
IF(
    ISFILTERED(IV_dim_CALENDAR_ResolvedDate[YearMonthShort])
    , 
        COUNTROWS(IV_fact_TicketsAll)+0
        , 
        CALCULATE(COUNTROWS(IV_fact_TicketsAll),IV_dim_CALENDAR_ResolvedDate[Is Current Month] = "True")+0
)

Issue I have is when we try and drill through. Rather than just picking out the relevant rows for month it pulls all rows that meet any over filters used on that KPI. Now to show correct values on a chart. I have to use the above code which works fine on a page but not in a drill through table of rows. If I add it PowerBI just keeps trying and then gives up. Even if only drilling through to 3 rows.

I'm assuming it cannot determine which it needs to use. Current month of the filter one. So it tries to pull all. If I use a measure which just counts the number of rows in main ticketsall fact table that would filter to correct number. However this will not be the current month nor a filtered one.

Does anyone have any ideas please?

Upvotes: 0

Views: 489

Answers (1)

Adrian lock
Adrian lock

Reputation: 3

To resolve the issue I turned to using a calculated group as described by Alberto Ferrari(see bellow link). This then gave an proper aggregation to all the tables with relevant date which in turn meant the it only pulled through the data for that month in the drill through.

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

Upvotes: 0

Related Questions