Forum Discussion

jayasurya_prud's avatar
jayasurya_prud
Icon for Advocate III rankAdvocate III
3 years ago
Solved

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 ...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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