add a column in table stored in direct query with data from another table stored in import mode

[josetabuenca]

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.

[josetabuenca]

Can someone help here?. Solutions provided so far didn't work

[v-yanjiang-msft]

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.

[josetabuenca]

Hi @v-yanjiang-msft 

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.

[v-yanjiang-msft]

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.

[josetabuenca]

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.

[amitchandak]

@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")

[josetabuenca]

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

[josetabuenca]

@amitchandak 

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?

[josetabuenca]

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.