Error msg when i try to combine 2 column data into 1 column

[admin11]

Hi All

Yesterday  @ALLUREAN 

help me on below post

https://community.powerbi.com/t5/Desktop/what-is-the-expression-for-BRAND-M/td-p/2256952

Today I try to create a expression :-

SEGMENT_M = 'SALES'[SEG] & 'SEGMENT'[SEGMENT_C]

Remark :- Above expression from 2 Table , SALES and SEGMENT Table

I get error msg :-

A single value for column 'SEGMENT_C' in table 'SEGMENT' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The reason i need to combine SEG and SEGMENT_C is because now i need 2 Table , After successfully combine , i can use SEGMENT_M as Dimension. So only table required.

Paul

[Anonymous]

Hi @admin11 ,

These two columns are from different tables, you will need to have a correspondence between them before you can merge them.

Please show some sample data so that we could help.

Best Regards,

Jay

[Ashish_Mathur]

Hi,

This should typically be a calculated column formula (not a measure) in the Sales table.

[admin11]

@Ashish_Mathur 

I cannot find calculated forumla :-

Hope you can share with me .

Paul

[Ashish_Mathur]

Click on the Data tab in the left hand side vertical panel, select the appropriate Table and then write the formula in that table.

[admin11]

@Ashish_Mathur 

I have try still get error . Do you mean below :-

[Ashish_Mathur]

Now click on the New Column button appearing in the ribbon.

[admin11]

@Ashish_Mathur 

I try to create the column , i still get error. See below image :-

[Ashish_Mathur]

Share the download link of your PBI file and show the exact result that you want.

[admin11]

@Ashish_Mathur 

I know the answer is to combine SEGMENT_C field from SALES Table and SEG field from SEGMENT Table, But i don't know how to write to code .

My PBI file :-

https://www.dropbox.com/s/4ufqp99gpnp9ek5/PBS_VER0252%20Ask%20how%20to%20combine%20SEG%20field.pbix?...

[Ashish_Mathur]

Hi,

Try this calculated column formula

SEGMENT_M = 'SALES'[SEG] & LOOKUPVALUE(SEGMENT[SEGMENT_C],SEGMENT[SEG_RAW],SALES[SEG])

Hope this helps.

[admin11]

@Ashish_Mathur 

Thank you very much for your expression , it work fine. for

SOURCE = LE , SD , PW ,PM, DA

But for SOURCE = TS , TA, TP , TI it display as "A02Business Partner" instead of "Business Partner"

Can you help me to add one more condition to below expression , that is only perform LOOKUPVALUE for SOURCE = LE , SD , PW ,PM, DA

SEGMENT_M = 'SALES'[SEG] & LOOKUPVALUE(SEGMENT[SEGMENT_C],SEGMENT[SEG_RAW],SALES[SEG])

Wish you happy new year

Paul

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Where is the source column - in which table?

[admin11]

@Ashish_Mathur 

Appreciate very much for your help. i encloed below link is the PBI sample file , with detail explanation in the page.

https://www.dropbox.com/s/wh91oq9alv9c5p2/PBS_VER0275.pbix?dl=0

Paul

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

SEGMENT_M_1 = if(SALES[SOURCE]="LE"||SALES[SOURCE]="SD"||SALES[SOURCE]="PW"||SALES[SOURCE]="PM"||SALES[SOURCE]="DA",'SALES'[SEG] & LOOKUPVALUE(SEGMENT[SEGMENT_C],SEGMENT[SEG_RAW],SALES[SEG]),LOOKUPVALUE(SEGMENT[SEGMENT_C],SEGMENT[SEG_RAW],SALES[SEG]))

[admin11]

@Ashish_Mathur 

Happy new year to you , Thank you very much , you code make my table look nice.

[Ashish_Mathur]

To you as well.  Glad to hear that my solution is working.

[bcdobbs]

You're getting the error because you're trying to combine two columns from different tables.

Assuming the tables are related:

Segment 1 => Many Sales

try the following dax:

SEGMENT_M = 'SALES'[SEG] & RELATED ( 'SEGMENT'[SEGMENT_C] )

[admin11]

@bcdobbs @Ashish_Mathur 

I try your propose expression :-

SEGMENT_M_ = 'SALES'[SEG] & RELATED ( 'SEGMENT'[SEGMENT_C] )

I get error msg below :-

The column 'SEGMENT[SEGMENT_C]' either doesn't exist or doesn't have a relationship to any table available in the current context.