Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Need help with measure / column

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

 

InvoiceIDProfileNumberInvoiceNumberInvoiceDate
10206931002049030397-Sep-22
10207951002049031377-Sep-22
10207771002079031237-Sep-22
10207301002089030787-Sep-22
10206111004009029817-Sep-22
10206991039009030447-Sep-22
10207471039009030967-Sep-22
10204911045009028997-Sep-22
10205121045009029177-Sep-22
10205491045009029367-Sep-22
10205631045009029447-Sep-22
10205651045009029467-Sep-22
10205741045009029527-Sep-22
10205941045009029637-Sep-22
10206041045009029727-Sep-22
10206351045009029957-Sep-22
10206381045009029977-Sep-22
10206541045009030067-Sep-22
10206551045009030077-Sep-22
10206621045009030097-Sep-22
10206811045009030297-Sep-22
10206831045009030317-Sep-22
10206881045009030367-Sep-22
10207201045009030657-Sep-22
10207211045009030667-Sep-22
10207331045009030797-Sep-22
10207461045009030957-Sep-22
10207521045009031027-Sep-22
10207581045009031077-Sep-22
10207821045009031287-Sep-22
10207941045009031437-Sep-22
10208221045009031747-Sep-22
10208261045009031837-Sep-22
10208321045009031947-Sep-22
10208341045009031967-Sep-22
10207731057009031217-Sep-22
10207391071009030857-Sep-22
10207481071009030977-Sep-22
10208201075019031357-Sep-22

 

Table 2

 

InvoiceIDDetailIDNumberField33Field34
10206812773985CHARGE 484296
102068127739861888189690 484296
102068327739913900974751/52 72725
10206832773992CHARGE 72725
102068827740033900974753 139452
10206882774004CHARGE 139452
102069327740213900974757198175 
10206932774022CHARGE198175 
102069927740493900970799253502 
10206992774050CHARGE  
102072027741083900974770 253502
10207202774109CHARGE 253502
102072127741103900974771 644958
10207212774111CHARGE 644958
102073027741333900970312476256 
102073327741453900974776 476256
102073327741463900974777 450642
10207332774147CHARGE  
10207332774148CHARGE  
102073927741663900974780450642 



 

Result (Sample Format)

NumberField
3900963926112045
CHARGE112045
39008108980
CHARGE0
3900963939563026
39009639400
39009639410
39009639420
CHARGE563026
CHARGE563026
CHARGE563026
Need Help
Show and Tell
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      gauravnarchal
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      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's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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