Forum Discussion
Can't determine relationship between fields? Very simple modeling issue
Hello all, and thanks in advance for any help.
I have (what seems like) a very simple problem and I'm banging my head trying to figure out why I'm getting an error. I have a model with 3 tables - (2) fact tables and (1) dim table to link them. The dim table is a duplicate of one of the fact tables with duplicate values removed. Here is what it looks like:
However, when I try to use these values in a visual, this is what I get:
The relationships from your dimension table aren't at fault. Using the same setup with a measure for example works fine:
Fact1Rows = COUNTROWS ( Fact1 )What it's really saying is that is doesn't know what you want to see and therefore can't write the DAX query in the background. If you just have Dim Doc# and one of the Fact Doc# the table visual writes the following (Copied and neatened up from View >> Performance Analyser:
EVALUATE SELECTCOLUMNS ( KEEPFILTERS ( FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'Fact2'[Doc#], 'Dim'[Doc#], "CountRowsFact2", CALCULATE ( COUNTROWS ( 'Fact2' ) ) ) ), OR ( NOT ( ISBLANK ( 'Fact2'[Doc#] ) ), NOT ( ISBLANK ( 'Dim'[Doc#] ) ) ) ) ), "'Fact2'[Doc#]", 'Fact2'[Doc#], "'Dim'[Doc#]", 'Dim'[Doc#] )In DAX studio you can write a query using all three which will run:
EVALUATE SELECTCOLUMNS ( KEEPFILTERS ( FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'Fact1'[Doc#], 'Fact2'[Doc#], 'Dim'[Doc#], "CountRowsFact2", CALCULATE ( COUNTROWS ( 'Fact2' ) ), "CountRowsFact1", CALCULATE ( COUNTROWS ( 'Fact1' ) ) ) ), NOT ( ISBLANK ( 'Fact2'[Doc#] ) ) || NOT ( ISBLANK ( 'Dim'[Doc#] ) ) || NOT ( ISBLANK ('Fact1'[Doc#] ) ) ) ), "'Fact2'[Doc#]", 'Fact2'[Doc#], "'Dim'[Doc#]", 'Dim'[Doc#], "'Fact1'[Doc#]", 'Fact1'[Doc#] )Don't think that fully answers your question but hope you can see that what you're hitting is effectively a limitation of the visual. Do you have a real situation you'd like to apply this to as there will be ways round it.
7 Replies
The relationships from your dimension table aren't at fault. Using the same setup with a measure for example works fine:
Fact1Rows = COUNTROWS ( Fact1 )What it's really saying is that is doesn't know what you want to see and therefore can't write the DAX query in the background. If you just have Dim Doc# and one of the Fact Doc# the table visual writes the following (Copied and neatened up from View >> Performance Analyser:
EVALUATE SELECTCOLUMNS ( KEEPFILTERS ( FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'Fact2'[Doc#], 'Dim'[Doc#], "CountRowsFact2", CALCULATE ( COUNTROWS ( 'Fact2' ) ) ) ), OR ( NOT ( ISBLANK ( 'Fact2'[Doc#] ) ), NOT ( ISBLANK ( 'Dim'[Doc#] ) ) ) ) ), "'Fact2'[Doc#]", 'Fact2'[Doc#], "'Dim'[Doc#]", 'Dim'[Doc#] )In DAX studio you can write a query using all three which will run:
EVALUATE SELECTCOLUMNS ( KEEPFILTERS ( FILTER ( KEEPFILTERS ( SUMMARIZECOLUMNS ( 'Fact1'[Doc#], 'Fact2'[Doc#], 'Dim'[Doc#], "CountRowsFact2", CALCULATE ( COUNTROWS ( 'Fact2' ) ), "CountRowsFact1", CALCULATE ( COUNTROWS ( 'Fact1' ) ) ) ), NOT ( ISBLANK ( 'Fact2'[Doc#] ) ) || NOT ( ISBLANK ( 'Dim'[Doc#] ) ) || NOT ( ISBLANK ('Fact1'[Doc#] ) ) ) ), "'Fact2'[Doc#]", 'Fact2'[Doc#], "'Dim'[Doc#]", 'Dim'[Doc#], "'Fact1'[Doc#]", 'Fact1'[Doc#] )Don't think that fully answers your question but hope you can see that what you're hitting is effectively a limitation of the visual. Do you have a real situation you'd like to apply this to as there will be ways round it.
Thanks, but I'm still not sure I understand. I created a fresh model similar to the one in my original post using a calendar table for the dim and two facts tables with duplicated dates in it. I then created custom columns with simple string values in each fact table and I still get the same error (below).
Ok how about thinking about it like this.
For a given date you can get a set of values from fact1.
eg
Date1 A
Date1 B
Date1 C
You can do the same with fact table 2 eg
Date1 D
Date1 E
Date1 F
If you ask power bi to put it all together in a single visual what result would you expect.
The only logical thing to do is some sort of cross join.
Hi, wbhite
You are using fields that are not related in this visual. Try using values from one of the fact table or try using a measure based on both fact tables.
Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.
Appreciate your Kudos !!!
"You are using fields that are not related in this visual."
I clearly am, unless I'm missing something obvious. Literally every field in the model is related.
Field from Fact1 and Field from Fact2 are not filter propagating and cannot be visualized. Just for the testing purposes, try to make one of the relationships bi-directional
Hi, wbhite
The way PBI filters work is: The 1: side table filters the N: side table. Filters propagate that way.But the 2 fact tables can't relate because the filters don't propagate that way.
Look into DAX measures like RELATED(), RELATEDTABLE() and USERELATIONSHIP() that might work for you.
The model described in the reference below is the same as yours, you can read it and get more.
Can't determine relationships between the fields
Error: Can't determine relationship between fields
Can’t determine relationships between the fields: bidirectional
More:
Determining Power BI Relationships
Best Regards,
Community Support Team _ Zeon ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
