Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

without relationship table

All, 

 

I have two tables,  table 2 'discount' amount should populate in table 1 using DAX Formula, please suggest.

don't want create relationship or merge two tables because data is huge size.

 

table 1                                                          

Regionquartersales
USCANQ1400
EMEAQ1200
CHINAQ1300
INTERNATIONALQ1500
USCANQ2300
EMEAQ2200
CHINAQ2500
INTERNATIONALQ2800
USCANQ3700
EMEAQ3300
CHINAQ3200
INTERNATIONALQ3800

 

 

table 2

 

Regionquarterdiscount
USCANQ140
EMEAQ120
CHINAQ130
INTERNATIONALQ150
USCANQ230
EMEAQ220
CHINAQ250
INTERNATIONALQ280
USCANQ370
EMEAQ330
CHINAQ320
INTERNATIONALQ380

 

 

result :

 

Regionquartersalesdiscount
USCANQ140040
EMEAQ120020
CHINAQ130030
INTERNATIONALQ150050
USCANQ230030
EMEAQ220020
CHINAQ250050
INTERNATIONALQ280080
USCANQ370070
EMEAQ330030
CHINAQ320020
INTERNATIONALQ380080
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,


    Thanks for the update totally understand that you're working with large data and can't share the file.

    If creating a relationship is giving you an ambiguous relationship error, that usually means there are multiple paths between the two tables maybe through some other common table or field in the model. Since you're avoiding relationships altogether, the LOOKUPVALUE approach should still work if the values in Region and Quarter match exactly between the two tables (including no extra spaces or case mismatches).

    Here’s a quick checklist to make sure the LOOKUPVALUE works as expected:

    1. Double check for any leading/trailing spaces in Region or Quarter columns in both tables.
      You can try adding TRIM() or use Power Query to clean text if needed.
    2. Make sure the combination of Region and Quarter is unique in the discount table if there are duplicates, LOOKUPVALUE might not behave consistently.
    3. Use this revised formula as a calculated column in Table1.
    Discount = 
    VAR _region = TRIM(Table1[Region])
    VAR _quarter = TRIM(Table1[Quarter])
    RETURN
    LOOKUPVALUE(Table2[Discount],Table2[Region], _region,Table2[Quarter], _quarter)

    Let me know how this goes, happy to help further if it still acts up.

    Regards,
    Akhil. 

7 Replies

  • alish_b's avatar
    alish_b
    Super User
    1 year ago

    Hi Anonymous ,

     

    I would advocate the use of relationships whenever possible. They are generally more performant.

    But that being said, you know your data better. Since you mentioned no relationships, please use the following DAX for calculated column in Table1.

     

    Discount = 
    LOOKUPVALUE(
        Table2[Discount],
        Table2[Region], Table1[Region],
        Table2[Quarter], Table1[Quarter]
    )

    RELATED will not work when there is no relationship established.

     

    Hope it helps!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      It not works and try to create relationship as getting error as "ambigous relationship"

      I can't share the file. will you find any best solution.

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,


    Thanks for the update totally understand that you're working with large data and can't share the file.

    If creating a relationship is giving you an ambiguous relationship error, that usually means there are multiple paths between the two tables maybe through some other common table or field in the model. Since you're avoiding relationships altogether, the LOOKUPVALUE approach should still work if the values in Region and Quarter match exactly between the two tables (including no extra spaces or case mismatches).

    Here’s a quick checklist to make sure the LOOKUPVALUE works as expected:

    1. Double check for any leading/trailing spaces in Region or Quarter columns in both tables.
      You can try adding TRIM() or use Power Query to clean text if needed.
    2. Make sure the combination of Region and Quarter is unique in the discount table if there are duplicates, LOOKUPVALUE might not behave consistently.
    3. Use this revised formula as a calculated column in Table1.
    Discount = 
    VAR _region = TRIM(Table1[Region])
    VAR _quarter = TRIM(Table1[Quarter])
    RETURN
    LOOKUPVALUE(Table2[Discount],Table2[Region], _region,Table2[Quarter], _quarter)

    Let me know how this goes, happy to help further if it still acts up.

    Regards,
    Akhil. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    Just checking back  were you able to test the LOOKUPVALUE formula after cleaning up the Region and Quarter columns with TRIM()? Did it resolve the mismatch issue or are you still seeing unexpected results? If it’s still acting up, we can look at alternative approaches like TREATAS or a mapping table.

    Regards,
    Akhil.

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi Anonymous ,

    just wanted to follow up to see if the cleanup and LOOKUPVALUE approach worked out for you. If the mismatches are still there, we can dive deeper maybe try TREATAS or set up a mapping table for a more robust fix. Let me know where things stand.

    Regards,
    Akhil.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    11 months ago

    Hi,

    This calculated column formula works

    =CALCULATE(SUM(Table2[discount]),FILTER(Table2,Table2[Region]=EARLIER(Table1[Region])&&Table2[quarter]=EARLIER(Table1[quarter])))

    Hope this helps.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi Anonymous ,

    I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

    Regards,
    Akhil.