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
Stiill a newbie in BI.
Table 1 is a table stored in direct query.
ID |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
Table 2 is stored in import mode
ID | CATEGORY |
4 | ES |
7 | PR |
9 | TR |
10 | SD |
I would like to add a column in table 1 obtaining the following result
ID | CATEGORY |
1 | N/A |
2 | N/A |
3 | N/A |
4 | ES |
5 | N/A |
6 | N/A |
7 | PR |
8 | N/A |
9 | TR |
10 | SD |
I have been trying to create columns into table 1 with conditions towards table 2 without success.
Any idea on how to do it?
Cheers.
Can someone help here?. Solutions provided so far didn't work
Hi @josetabuenca ,
Please try this one:
CATEGORY =
COALESCE (
MAXX (
FILTER ( 'Table2', 'Table2'[ID] = EARLIER ( Table1[ID] ) ),
'Table2'[CATEGORY]
),
"N/A"
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot for your support.
I am obtainining same error message than before
The column 'Table1'[CATEGORY] cannot be pushed to the remote data source and cannot be used in this scenario.
Guessing here that Table1 stored in Direct Query is having an effect here.
Hi @josetabuenca ,
According to your description, here's my solution.
Add a calculated column in Table1:
CATEGORY =
COALESCE (
LOOKUPVALUE ( 'Table2'[CATEGORY], 'Table2'[ID], 'Table1'[ID] ),
"N/A"
)
It has no matter with the relationship, get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
Solution does not work. I receive following message
The column 'Table1'[CATEGORY] cannot be pushed to the remote data source and cannot be used in this scenario.
@josetabuenca , if relation between table 2 and table 1 is 1 to Many
then yoy can try
related(Table2[CATEGORY])
or
coalesce(related(Table2[CATEGORY]) , "N/A")
Hi
Thanks for your swift reply.
The relationship between Table 2 and Table 1 is Many to 1.
I cannot change the relation to 1 to Many. I got following error message: The cardinality selected isn't valid for this relationship.
Some another idea here?
Regards
I have been able to change the relation to 1 to Many by data cleaning ID column in Table 2 (remove duplicates and remove nulls),
However solution is still not working, I am obtaining following error message
The column 'Table1[CATEGORY]' either doesn't exist or doesn't have a relationship to any table available in the current context.
There us a relationship 1 to many between Table 2 and Table 1.
Some idea here?
correction
error message is The column 'Table2[CATEGORY]' either doesn't exist or doesn't have a relationship to any table available in the current context.
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 |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |