Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi.
I have 2 tables: 1st table (name: Page): loading from Power query into Power Pivot
2nd table (name: Raw_LT): loading from excel file into Power Pivot
Both table have 1 column (name: Index) that have the same information
I already make connection between 2 table in power Pivot
However, when I use Dax formulas in 1st table : =Related(Raw_LT[Region]), it was advised that the Raw_LT doesn't exit or there is no connection between 2 tables?
Is there any mistake that I made here ? and how to fix it.
Thanks in advance for your support
Solved! Go to Solution.
Hi @ngocnguyen
I can't access the file due to the limited permission.
The relationship between 2 tables is one -> many.
Which means that for each row in index you can have more than one row in another index.
Therefore, you must use RELATEDTABLE instead of related, And since this is going to return more than one row , You should wrap it in an aggregation function.
You can also refer to below similar threads for references:
https://community.powerbi.com/t5/Desktop/Table-Relationship-Doesn-t-Work/td-p/593189
Hi @ngocnguyen
I can't access the file due to the limited permission.
The relationship between 2 tables is one -> many.
Which means that for each row in index you can have more than one row in another index.
Therefore, you must use RELATEDTABLE instead of related, And since this is going to return more than one row , You should wrap it in an aggregation function.
You can also refer to below similar threads for references:
https://community.powerbi.com/t5/Desktop/Table-Relationship-Doesn-t-Work/td-p/593189
@ngocnguyen , related work from 1-M direction. I do not think it works form M to 1
in such If I have move data from one table to another in new column we use
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])
@amitchandakAs I checked again, it is actual M to 1 direction. However, I wonder why it is M to 1 direction, not 1 to M or 1 to 1 direction because I create 1 colume" Index" in both file that contain the unique value only.
So, is there any way to change it into 1 to M or 1 to 1 direction in Power Pivot?
P/s: If I load 2 tables from excel, there is no problem when using Dax Related. However, If 1st table load from Power query, 2nd table load from Excel, the above issue happens
Hi @ngocnguyen ,
Can you share your dax expression please, if you are using any?
Did you try making the relationship bi-dirrectional.?
Thanks,
Pragati
@Pragati11, About is my file. I try to make the relationship between 2 tables, but it can not work
Pls help
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |