Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I use Direct Query to get data from SQL Server. I was trying to create a relationship between two tables on a key column, which is a primary key in one table and foreign key in the other. It's a straight forward one-one relationship between them.
But, when I tried to create a relationship in Power BI desktop, it gave me a warning.
I tried changing the Cardinality, filter direction to different combinations, but it doesn't work.
Does anybody have any idea about this kind of warning?
Thanks.
If it is a one to one relationship, change it to one to one. Also, remember that filters flow downhill. are both of these fact tables or is one a look up table? How do you plan to use them? You may need to create a lookup table and link the two between them.
If you provide more information on your data we can better help you.
Proud to be a Super User!
@hvan-user2 Have you considered merging the tables into one look up table?
Proud to be a Super User!
Merging them into a single table will impact the performance. Every time I need something from those 25000 records, my queries will scan 4 million records.
Merging them can be a solution but not a perfect one.
Thanks for your help.
@hvan-user2Do you need all of the 4 million rows everytime? We have some robust data models here as well but I hardly ever cross over the million row mark in actual usage. I generally keep my data models smaller and more agile. I cannot imagine having a look up table that large. Can you share a snippet of data? If you can, the solution could be more specific. In fact, you might be better served with a snowflake data model.
Proud to be a Super User!
The business team will need them for various vetting purposes. And I'm sorry I can't share any data.
Thanks for your suggestion about snowflake data model.
All this discussion is good, but I would still like to know what caused the warning when I tried to create the relationship.
Hi hvan-user2,
Have you enabled the cross filtering in both directions for DirectQuery Preview option?
To enable cross-filtering for DirectQuery in Power BI Desktop, select File > Options and settings > Options, then check the box next to Enable cross filtering in both directions for DirectQuery, as shown in the following image.
For more information, check:
Bidirectional cross-filtering using DirectQuery in Power BI Desktop (Preview)
Enable this option and then check to see if the relationship could be created.
Hi,
Enable Cross Filter in both directions for DirectQuery was already enabled when I encountered that issue.
Not much help from enabling and disabling that feature. It still didn't let me create that relationship.
Thanks for your suggestion though.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |