DAX - query help

[jayasurya_prud]

Hi, I am working on a report with two tables and joining them with left join in sql

table  1

Hike_Year	Name	Marks
2017	john	90
2018	B	91
2014	A	92
2015	B	93
2014	C	94
2015	D	95
2018	E	96
2019	F	97
2014	E	98
2019	F	99
2015	C	100
2016	D	101
2014	G	102
2017	H	103
2014	john	104

table 2 

Joined Year	Name	Marks
2014	A	10
2014	c	11
2014	E	12
2014	john	13

when I join this table to gether by left join by on clause with year

Hike_Year	Name_1	Marks_1	Joined Year	Name_2	Marks_2
2017	john	90
2018	B	91
2014	A	92	2014	A	10
2015	B	93
2014	C	94	2014	c	11
2015	D	95
2018	E	96
2019	F	97
2014	E	98	2014	E	12
2019	F	99
2015	C	100
2016	D	101
2014	G	102
2017	H	103
2014	john	104	2014	john	13

now i need to calculate the average of Marks_2 column in the joined table.

But I dont have information more than 2014 in thr table 2. 

The present main year of the calculation is 2017 in the table 1.

Since I dont have the information on table 2 of 2017, I am gonna use 2014 as proxy for 2017

So I need to create a dax to calculate the average of the Marks_2 column in dax where the names are same in 2014 and 2017. 

In the above joined table, I have only John as present in 2017 and 2014 after joining the table. 

How create the dax with the condition.

[v-yangliu-msft · Accepted solution]

Hi  @jayasurya_prud ,

Here are the steps you can follow：

1. Create calculated column.

Avg =
var _select=SELECTCOLUMNS(FILTER(ALL(Table1) ,'Table1'[Hike_Year]=2017),"Name",[Name])
return
AVERAGEX(
    FILTER(ALL(Table2),
    'Table2'[Name] in _select &&
    'Table2'[Name]=EARLIER('Table1'[Name])&&
    'Table1'[Hike_Year]=2017),[Marks])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

So you want to use prior results as a proxy if data is missing?  Please show the expected outcome based on your sample data.

[jayasurya_prud]

Thank you for the response. 

So in the above the joined table, for the column Joined Year, I only have 2014. No data after that. So What I am planning to do is calculate the average of the names which are present in both Joined year 2014 and Hike Year 2017 - Which is, in the table, I have only one name which is present in both records - It is John. 

So my dax should get an average from john. 

As this is the sample/dummy data, I am having only one name, But I may have a lot of names in the real data. So the average should be calulated in the Joined_Year column only when the names are similarly / present for both 2014 in Joined Year column and 2017 in Hike Year col. 

Hope this clarifies.

[lbendlin]

What would be the average for John? 97?

[jayasurya_prud]

No. In marks 2 column it is 13 right, so my avg should come only 13. 

Likewise, I need to calculate the average of marks _2 column, only for the names present both in 2014 in joined year, name_2 and 2017 in Hike year and name column. 

So in the above table, only John present in hike year 2017 and 2014 in joined year.