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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors