Better understanding of the filter context in the following SUMX

[alexbjorlig]

I'm trying to develop a measure where I get the following results (will show you data and attached power bi report afterwards).

Data is shaped the follwing way:

My first objective for the measure; If the row-context includes a reportKey, should include a DISTINCTCOUNTNOBLANK of all ActionKey, like this:

My second objective for the measure; If the row-context does not include a reportKey, should include a DISTINCTCOUNTNOBLANK of all ActionKey from the latest report:

 

My attempt to accomplish the above:

Count = SUMX(Report,
        VAR LatestReport = CALCULATE(MAX(Report[Date]), ALL(Report)) RETURN 
    CALCULATE(
        DISTINCTCOUNTNOBLANK('Fact'[ActionKey])
        ,Report[Date] = LatestReport
    )
)

So in other words, why does it seem like the above measure ignores the LatestReport filter context?

Here is the link for the workbook.

And thanks in advance 😅

[v-cazheng-msft]

Hi @alexbjorlig 

May I know whether the answers we provide here helps you? If it helps, could you please kindly  Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks in advance!

Best Regards,

Community Support Team _ Caiyun

[v-cazheng-msft]

Hi @alexbjorlig 

Generally, we recommend minimizing the use of bi-directional relationships unless you are relating one-to-one or many-to-many tables. When you using bi-directional relationship, it will bring some negatively impact on model query performance and sometimes it will deliver some confusing results for the filters will be passed in two directions. For more details about Bi-directional relationship, you may refer to Bi-directional relationship guidance - Power BI | Microsoft Docs. Also, there is an example in this document will help you understand it. If your tables are one-to-many, it would be better for you to keep the direction single direction except it is necessary for your scenario.

Take your sample as an example. You can compare the difference of max date calculated in your Measure when the direction is single or both.

When the direction is single, only filters from Report will be passed to Fact table but filters from Category and Fact tables won’t be passed to Report table, so Report_Max_date = CALCULATE(MAX(Report[Date]), ALL(Report)) is calculated max date for all the ReportKey in Report table and will not be impacted by other filters.

The max date will be 1/1/2021 whatever the CategoryKey is.

When the direction is Both, something happens when calculated the max date. Filter of CagegoryKey will be passed to Report table, so the max date will be calculated based on this filter as well.

The max date will be impacted by CategoryKey.

This is why lbendlin suggested you making some changes to your data model and calculating what you want in your first objective with a new Measure.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!

[alexbjorlig]

Thank you so much. As a former Qlik user, I could never imagine a world where slicers did not update as you perform selections. But thanks to your link, I now learned that I can achieve that simply using a visual filter on the slicer.

I still think it's unfortunate that the current understanding of what happens is "something happens", but if everyone else is developing wihtout bi-directional filters in Power BI I guess I will have to adapt 🤓 

[v-cazheng-msft]

Hi @alexbjorlig 

Perhaps it is the magic of Power BI, results of its formula will be impacted by the calculation environment which mainly consists of filters including filters in tables' relationships.

In fact, there is a small tip that can help you understand the single and both relationships, which is the small arrow on the line between the two tables. By referring to the direction of the arrow, we can understand whether the filter conditions can be passed from one table to another table along the relationship between the tables.

Best Regards,

Community Support Team _ Caiyun

[lbendlin]

There's a reason why we have Qlik, Tableau and Power BI, and SAS, etc - they all have different philosophies and underlying engines. I actually find it quite fascinating to see how many different ways there are to configure a data structure.

At the end of the day your business problem dictates which of the available tools is most appropriate for that particular situation.

[lbendlin · Accepted solution]

A jolly good conversation can be had on the pros and cons of using enumerator functions vs Calculate. Doesn't really matter that much.  The LatestReport variable definition should have happened outside the SUMX context.

Here is the start of an alternative option (use ISINSCOPE() to add your report key trickery)

Count = 
VAR LatestDate = CALCULATE(MAX(Report[Date]), ALL(Report))
RETURN CALCULATE(COUNTROWS(VALUES('Fact'[ActionKey])),Report[Date]=LatestDate,'Fact'[ActionKey]<>BLANK())

You may also want to modify your data model a bit.

[alexbjorlig]

I am exploring ISINSCOPE() --> looks pretty promising. Why would you change the data model, to not use bi-directional filtering?