CALCULATE with external filters

[darbie]

Hi All,

I have following table as data source:

I have defined a measure to check if particular item can be found also in the "next date":

Is in next day = 
VAR CTYPE = VALUES('Table'[Type])
VAR current_date = SELECTEDVALUE('Table'[Date])
VAR next_date = CALCULATE(MIN('Table'[Date]);'Table'[Date]>current_date)

RETURN CALCULATE(COUNTROWS('Table');'Table'[Date]=next_date; 'Table'[Type] in CTYPE)

It works fine at first glance, e.g.:

So far so good, i.e. in column "Is in next day" the measure is working fine. The problem happens, when I set a filter (e.g. on page level), for value which is not actually used by the measure (in our case it is Color):

As you see I have used filter that color should not be "Black". In the model, type BBB has only color Blue or White so should not be impacted by this filter at all. But apparently the measure does not work correctly when the color is changed (in our case it is between 06.06.2020 and 07.06.2020):

As you see, there are no other filters applied, but still the measure results blank() in 06.06.2020. What is the reason for that? If I remove the page filter for Color then problem is not visible (as mentioned above). Is this expected behavior? How to overcome this issue?

Many thanks in advance!

[v-lili6-msft]

hi @darbie 

For your case, you just adjust it as below:

Step1:

Create a date table and create a relationship with basic table

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Step2:

then adjust the formula as below:

Is in next day = 
VAR CTYPE = VALUES('Table'[Type])
VAR current_date = SELECTEDVALUE('Date'[Date])
VAR next_date = CALCULATE(MIN('Date'[Date]),'Date'[Date]>current_date)

RETURN 
CALCULATE(COUNTROWS('Table'),'Date'[Date]=next_date, 'Table'[Type] in CTYPE)

now when create visuals, use date field from date table.

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[darbie]

Thanks! Though this looks to be kind of workaround rather than solution. In other words - why the problem occurs in my case with single table only (is it expected behavior of PBi?)? Why do I need a second table in my case?