Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |