Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
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
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,
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??
what filter u applied..can u be more clear on ur question
Any help on this at all?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |