Forum Discussion
DAX - query help
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.
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
5 Replies
So you want to use prior results as a proxy if data is missing? Please show the expected outcome based on your sample data.
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.What would be the average for John? 97?
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
