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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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's avatar
    jayasurya_prud
    Icon for Advocate III rankAdvocate III
    3 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      What would be the average for John? 97?

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

        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.