cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
josetabuenca
Regular Visitor

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

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

IDCATEGORY
4ES
7PR
9TR
10SD

 

I would like to add a column in table 1 obtaining the following result

IDCATEGORY
1N/A
2N/A
3N/A
4ES
5N/A
6N/A
7PR
8N/A
9TR
10SD

 

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.

9 REPLIES 9
josetabuenca
Regular Visitor

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

v-kalyj-msft
Community Support
Community Support

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.

Hi @v-kalyj-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-kalyj-msft
Community Support
Community Support

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:

vkalyjmsft_0-1670566403878.png

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.

amitchandak
Super User
Super User

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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

@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?

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.