topic Re: Blank table visual in the drillthrough page when using a measure in DAX Commands and Tips

Blank table visual in the drillthrough page when using a measure

mariella2 — Fri, 08 Mar 2024 17:18:31 GMT

Hi all, I have a two pages report. The first page is for summary data and the second for detailing data (Drillthrough page).

In the summary page, I have a bar chart having Calendar [Month Year] field on the x axis and a measure of Overdue tasks on the y axis.

Overdue =

CALCULATE(

COUNTROWS('Fact'),

FILTER(

ALLSELECTED('Fact'),

MONTH('Fact'[Date Closed]) = MONTH(MAX('Calendar'[Date]))

&& YEAR('Fact'[Date Closed]) = YEAR(MAX('Calendar'[Date]))

&& 'Fact'[Date Closed] > 'Fact'[Expected Completion Date ]

)

+ CALCULATE(

COUNTROWS('Fact'),

FILTER(

ALLSELECTED('Fact'),

ISBLANK('Fact'[Date Closed])

&& MAX('Calendar'[Date]) > 'Fact'[Expected Completion Date ]

)

+ CALCULATE(

COUNTROWS('Fact'),

FILTER(

ALLSELECTED('Fact'),

'Fact'[Date Closed] > MAX('Calendar'[Date])

&& MAX('Calendar'[Date]) > 'Fact'[Expected Completion Date ]

)

When I drillthrough on the bar chart, I get an empty table on the drillthrough page. I have checked all the bars in the visual and getting the same result. The drillthrough page has the Overdue measure on the page info, in order to drillthrough for that context.

The table consists of the id field, date opened, date closed, Expected Date fields, and the Overdue measure.
If I remove the Overdue measure from the table, I get rows based on the Month Year from the bar chart for the Date Opened. So, if I select the bar for December 2022 in the bar visual, the drillthrough table shows records with Date Opened in December 2022 instead of showing the results from the Overdue measure calculation.
In the report model, there is an active relantionship between Date Opened and Calendar [Date]. The other 2 Date fields have an inactive relantionships.

I have tried to add to the overdue measure the REMOVEFILTERS('FACT'[DATE OPENED]) for each of the three calculations inside the measure, or to use CROSSFILTER('CALENDAR'[DATE], FACT[DATE OPENED],NONE), still it didn't work.

I've came across this link: Criss Blog
I think it could be the solution to my issue, but I am unable to replicate the measure to use in my table visual in the drillthrough page.
If you could please help me out, I'm really grateful.

Re: Blank table visual in the drillthrough page when using a measure

some_bih — Fri, 08 Mar 2024 20:05:50 GMT

HI mariella2

You try to figure out with measure Overdue some details, and using drillthrough page?

It could be that measure is not working (ALLSELECTED is used - "the most complex" function in DAX) or your setup for drillthrough is not ok, check some link for this

I am curios: what is logic behind

'Fact'[Date Closed] > MAX('Calendar'[Date])

Usually, Calendar date is equal or higher than some date in Fact table

Re: Blank table visual in the drillthrough page when using a measure

mariella2 — Fri, 08 Mar 2024 20:16:48 GMT

Thanks for your reply. The logic behind this:
'Fact'[Date Closed] > MAX('Calendar'[Date])

is to calculate those records overdue where the Date closed is higher than the max calendar date for a specific period and at the same time MAX('Calendar'[Date]) > 'Fact'[Expected Completion Date ].

If the closed date is higher than the Calendar Date, it does mean that it was blank at the time, and if at the same time MAX('Calendar'[Date]) > 'Fact'[Expected Completion Date ], it means that it was overdue.

"You try to figure out with measure Overdue some details, and using drillthrough page?"
The bar chart where the Overdue measure is in the Summery page, selecting a bar, I want to drillthrough those data in the drillthrough page.
In the Overdue measure, I have used also ALL, with the same results of ALLSELECTED.

I'll have a look at the link you posted.