Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |