Need help with measure / column

[gauravnarchal]

I need help to create a measure or column to find the value from different field columns based on the profilenumber.

For example:-  If profilenumber in table 1 is "104500" then the value should be from the table 2 column field34 else the value should be from field33.

Table 1

InvoiceID	ProfileNumber	InvoiceNumber	InvoiceDate
1020693	100204	903039	7-Sep-22
1020795	100204	903137	7-Sep-22
1020777	100207	903123	7-Sep-22
1020730	100208	903078	7-Sep-22
1020611	100400	902981	7-Sep-22
1020699	103900	903044	7-Sep-22
1020747	103900	903096	7-Sep-22
1020491	104500	902899	7-Sep-22
1020512	104500	902917	7-Sep-22
1020549	104500	902936	7-Sep-22
1020563	104500	902944	7-Sep-22
1020565	104500	902946	7-Sep-22
1020574	104500	902952	7-Sep-22
1020594	104500	902963	7-Sep-22
1020604	104500	902972	7-Sep-22
1020635	104500	902995	7-Sep-22
1020638	104500	902997	7-Sep-22
1020654	104500	903006	7-Sep-22
1020655	104500	903007	7-Sep-22
1020662	104500	903009	7-Sep-22
1020681	104500	903029	7-Sep-22
1020683	104500	903031	7-Sep-22
1020688	104500	903036	7-Sep-22
1020720	104500	903065	7-Sep-22
1020721	104500	903066	7-Sep-22
1020733	104500	903079	7-Sep-22
1020746	104500	903095	7-Sep-22
1020752	104500	903102	7-Sep-22
1020758	104500	903107	7-Sep-22
1020782	104500	903128	7-Sep-22
1020794	104500	903143	7-Sep-22
1020822	104500	903174	7-Sep-22
1020826	104500	903183	7-Sep-22
1020832	104500	903194	7-Sep-22
1020834	104500	903196	7-Sep-22
1020773	105700	903121	7-Sep-22
1020739	107100	903085	7-Sep-22
1020748	107100	903097	7-Sep-22
1020820	107501	903135	7-Sep-22

Table 2

InvoiceID	DetailID	Number	Field33	Field34
1020681	2773985	CHARGE		484296
1020681	2773986	1888189690		484296
1020683	2773991	3900974751/52		72725
1020683	2773992	CHARGE		72725
1020688	2774003	3900974753		139452
1020688	2774004	CHARGE		139452
1020693	2774021	3900974757	198175
1020693	2774022	CHARGE	198175
1020699	2774049	3900970799	253502
1020699	2774050	CHARGE
1020720	2774108	3900974770		253502
1020720	2774109	CHARGE		253502
1020721	2774110	3900974771		644958
1020721	2774111	CHARGE		644958
1020730	2774133	3900970312	476256
1020733	2774145	3900974776		476256
1020733	2774146	3900974777		450642
1020733	2774147	CHARGE
1020733	2774148	CHARGE
1020739	2774166	3900974780	450642

Result (Sample Format)

Number	Field
3900963926	112045
CHARGE	112045
3900810898	0
CHARGE	0
3900963939	563026
3900963940	0
3900963941	0
3900963942	0
CHARGE	563026
CHARGE	563026
CHARGE	563026

[Greg_Deckler]

@gauravnarchal Maybe:

Measure =
  VAR __InvoiceID = MAX('Table2'[InvoiceID])
  VAR __Profile = MAXX(FILTER(ALL('Table1'),[InvoiceID] = __InvoiceID),[ProfileNumber])
RETURN
  IF(__Profile = "104500", SUM('Table2'[Field34]), SUM('Table2[Field33]))

[gauravnarchal]

Hi @Greg_Deckler  - I do not need to SUM field 33 or field 34. instead just wanted to move the actual value into a new column.

[v-easonf-msft · Accepted solution]

Hi， @gauravnarchal 

If table1 and table2 are related based on field ’InvoiceID‘, you can create calculated columns in table2 as shown below.

Calculated column:

ProfileNumber = RELATED(Table1[ProfileNumber])

Field = if(Table2[ProfileNumber]=104500,Table2[Field34],Table2[Field33])

Please explain further if I misunderstood.

Best Regards,
Community Support Team _ Eason