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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Pateen
Helper I
Helper I

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

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?

5 REPLIES 5
Pateen
Helper I
Helper I

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.

 

Model.jpg

 

Working join (relationship)

 

working join.JPG

 

 

Non working Join

 

Not working join.jpg

 

 

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

 

result set.jpg

 

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,

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

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??

 

comapre tables.JPG

Anonymous
Not applicable

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

Any help on this at all?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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