Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
darbie
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!

 

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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:

2.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.