Create a measure that looks for a value in another table

[DRossi]

Hello,

I have three tables: Date table, Jobs Table and Rebate Table.

In my Dates table I have a Year QTR column, of which I have used the same in Rebate Period and there is a relationship (many to many) that is active between Rebate Period in the Rebate Table and Year QTR in the Dates Table.

Essentially, I am trying to create a measure that finds the model that was installed (in the jobs table) and from the install date (date), find the Rebate value from the relevant rebate period.

I did create a measure but it was summing the models Rebate Value for every Rebate Period when it should only look at the relevant quarter and the corresponding value for that model. Basically, in green is how I need that to llok, either through a measure or new column.

Rebate Value = CALCULATE(SUMX(RebateAmounts,RebateAmounts[Amount]),USERELATIONSHIP(RebateAmounts[Model],LocalHeroes_Warranty[boilerModel]))

Example below:

Jobs Table

JobID	Model	Date	*REBATE VALUE*
1	GStar 20si	01/08/21	300
2	GStar 25si	03/08/21	350
3	GStar 20si	08/08/21	300

Rebate Table

Model	Rebate Period	Rebate Value
GStar 20si	2021 QTR2	300
GStar 25si	2021 QTR2	350
GStar 30si	2021 QTR2	400
GStar 20si	2021 QTR3	350
GStar 25si	2021 QTR3	400
GStar 30si	2021 QTR3	450

[DRossi]

Hi @amitchandak  thanks for this.

I've added a date in the table and created a Many to One to the dates table.

I've added your DAX but now it is summing the whole RebateAmounts Table. It shouldn't sum, it should only display the corresponding rebate value for that model for that quarter.

I used LOOKUP

Rebate Value = LOOKUPVALUE(RebateAmounts[Amount],RebateAmounts[Model],LocalHeroes_Warranty[boilerModel])

[DRossi]

Hmm I might just have it

Rebate Value = LOOKUPVALUE(RebateAmounts[Amount],RebateAmounts[Model],LocalHeroes_Warranty[boilerModel])

[amitchandak · Accepted solution]

@DRossi , First Avoid many to many join , between date and table, create a date based on month, qtr or year and use that.

First few min this video will give that

https://www.youtube.com/watch?v=yPQ9UV37LOU

You can try measure like

Rebate Value = CALCULATE(SUMX(RebateAmounts,RebateAmounts[Amount]),Filter(RebateAmounts , RebateAmounts[Model] in allselected( LocalHeroes_Warranty[boilerModel])))

or use treatas

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/