Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

CALCULATE with external filters

Hi All,


I have following table as data source:

2020-06-10 16_37_13-Untitled - Power BI Desktop.png


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.:

2020-06-10 16_42_14-Untitled - Power BI Desktop.png


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):

2020-06-10 16_45_15-Untitled - Power BI Desktop.png

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):

2020-06-10 16_47_58-Untitled - Power BI Desktop.png


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!


Community Support
Community Support

hi @darbie 

For your case, you just adjust it as below:


Create a date table and create a relationship with basic table

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


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)

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


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



and here is sample pbix file, please try it.




Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

Helpful resources

Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.