Measure for visual card in a drill-through page not showing filtered values as expected

[mariella2]

Hi, I am building a report in Power Bi desktop with only two pages, one to display data and the other page is a drillthrough page. 

In the first page I have a bar chart with the column 'Risk Owner' on X axis and on the other a measure that calculates how many risks are open on a specific semester.

This is the measure I use:

Open Risks = CALCULATE(COUNTx(FILTER('Risk','Risk'[Creation Date]<= max('Calendar'[Date]) && (ISBLANK('Risk'[Mitigated Date]) || 'Risk'[Mitigated Date]> max('Calendar'[Date]))),[Risk ID]),CROSSFILTER('Calendar'[Date],'Risk'[Creation Date],None))

I also have a slicer on the first page with Caledar[Date] as a filter and a pie chart to show Open risks per category.

The column Creation Date in the Risk table has an active relantionship with Calendar[Date] and an inactive relantionship with Risk[Mitigated Date] column.

From the bar chart, if I select an Owner and click drill-through, the reports navigated to the drill-through page where there are two visual cards, one to show the owner's name and the other to show the risk category, and also a table to show the risks details for that particular owner (Risk ID, description, category, mitigated, rating). 

The measure I use in one of the visual card is (the other is the same but with Risk Category instead of Risk Owner):

If(Isfiltered(Risk[Risk Owner]),selectedvalue(Risk[Risk Owner], " ")," ")

The reason I use this formula is that when I filter the pie chart from the first page, I want the risk owner card to be blank and to show only the visual card with the category I am filtering (the same works for the other way around).

The problem I have is that the visual card doesn't display the owner's name when his/her risks have not been created in the semester selected in the slicer from the first page. If an owner has 3 risks and one of these has been created in the selected semester, then the card gets populated, otherwise it won't. When it doesn't get populated, the table instead gets populated and, if I select a record from the table, it also won't display the owner's name on the card visual. 

The same issue happens on the visual card for the Risk Category.

Example of not showing the risk owner below if I filter on the slicer from the first page for first half/year 2023:

The columns used as filters for the drill-through page are the Risk Owner, the measure for Open Risks, Risk Category, and automatically the Calendar[Date] from the slicer from the first page.

What can I do to fix this issue? Is there a way to add something like this in the measure for the visual card to filter:

Risk[Creation Date] <= Calendar[Date]  ?

Thanks in advance for your help.

[johnt75]

You could do that with

Selected Risk Owner =
CALCULATE (
    SELECTEDVALUE ( Risk[Risk Owner], " " ),
    Risk[Creation Date] <= Calendar[Date]
)

Another option if you didn't want any filtering on date is to remove calendar[date] from the drillthrough filter box.

[mariella2]

Thanks for your suggestions.

Using your measure, I get this error in yellow with the column Date from the Calendar underlined in red (when I hover on it it displays 'Cannot find name Date'):

The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.

Edit: I added max before Calendar[Date], the error is gone but still doesn't display the owner.

The second solution is not viable because it automatically goes back in the drill-through filters if I drill-through again.
Any other suggestion please?

[johnt75]

Try

Selected Risk Owner =
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SELECTEDVALUE ( Risk[Risk Owner], " " ),
        Risk[Creation Date] <= MaxDate
        REMOVEFILTERS( 'Calendar' )
    )

[mariella2]

Thanks, but the card is still blank. 😓

[johnt75 · Accepted solution]

Edited my post to include REMOVEFILTERS.

[mariella2]

Thanks a lot!!!! It works!!! I really appreaciate it!