without relationship table

[RajK2]

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                                                          

Region	quarter	sales
USCAN	Q1	400
EMEA	Q1	200
CHINA	Q1	300
INTERNATIONAL	Q1	500
USCAN	Q2	300
EMEA	Q2	200
CHINA	Q2	500
INTERNATIONAL	Q2	800
USCAN	Q3	700
EMEA	Q3	300
CHINA	Q3	200
INTERNATIONAL	Q3	800

table 2

Region	quarter	discount
USCAN	Q1	40
EMEA	Q1	20
CHINA	Q1	30
INTERNATIONAL	Q1	50
USCAN	Q2	30
EMEA	Q2	20
CHINA	Q2	50
INTERNATIONAL	Q2	80
USCAN	Q3	70
EMEA	Q3	30
CHINA	Q3	20
INTERNATIONAL	Q3	80

result :

Region	quarter	sales	discount
USCAN	Q1	400	40
EMEA	Q1	200	20
CHINA	Q1	300	30
INTERNATIONAL	Q1	500	50
USCAN	Q2	300	30
EMEA	Q2	200	20
CHINA	Q2	500	50
INTERNATIONAL	Q2	800	80
USCAN	Q3	700	70
EMEA	Q3	300	30
CHINA	Q3	200	20
INTERNATIONAL	Q3	800	80

[v-agajavelly]

Hi @RajK2 ,

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.

[Ashish_Mathur]

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.

[v-agajavelly]

Hi @RajK2 ,

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.

[v-agajavelly]

Hi @RajK2 ,

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.

[v-agajavelly · Accepted solution]

Hi @RajK2 ,

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. 

[alish_b]

Hi @RajK2 ,

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!

[RajK2]

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.