Cross filter direction = ‘Both’ works for one join in a schema but not a 2nd join in the same schema

[Pateen]

Hi There,

I have 3 tables:

One is a fact table called DAILY_TRANSACTION and the other two are lookup tables called PROFIT CENTRE and TRANSACTION TYPE

It is a Query Direct connection to SQL Server and in my data model I have set the cross filter direction to “Both” for my two joins

Now I want to view my data in a report:
My first report table contains a Profit Centre Code from the Fact table and a Profit Centre Code from the lookup table.
I now filter on the Profit Centre Code in the lookup table in data model to exclude values relating to profit centres coming from my fact table and it works fine on refresh.

For Table 2 of my report however the cross filter direction to “Both” doesn't seem to work.
Again the table containing Trans Type Code from the lookup table and the Trans Type Code from the fact table.

When I filter some transaction types in my lookup table in the data model the filter is only applied to the lookup table and not the fact table so it seems to represent an outer join.

Any help Please or I will have no hair left?

[Pateen]

Haven’t been able to figure this out at all.

Summary:

A filter applied to a lookup works perfectly for one side of my data model when I report the data but not the other side.

Any help on this would be great.

Working join (relationship)

Non working Join

Report result set from non-working join when filter applied to look-up table

[v-cherch-msft]

Hi @Pateen 

You may try to change the cross filter direction to single.Here is the article about What is the Direction of Relationship in Power BI? for your reference.

Regards,

[Pateen]

Hi @v-cherch-msft 

Thanks for your reply but it still doesn't resolve consistent data problems.

We now may have to look at another BI solution for our users.

To make our probelm much simpler, can anyone explain why there is a difference in both the exact same tables (below) copied from a report on the same page?

A filter has been applied to the look up value of Profit Centre in our data model with cross filter direction set to both.

In the second table below, the filter applied seems to work on both profit centre values.

In the first table, PROFIT_CTR_CD comes from the fact table so the join seems inconsistient??

[Anonymous]

what filter u applied..can u be more clear on ur question

[Pateen]

Any help on this at all?