Forum Discussion

Hariharan_R's avatar
Hariharan_R
Solution Sage
7 years ago

Filter and Dynamic Result

Hi 

 

  I have below tables and i need to get "Default/Bonus" (red colour columns) column on Trans table. Also mentioned the logic for the columns.

 

  We will be using RLS to filter the student.

 

Need Help

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Create a measure in Trans and copy paste following dax:

     

    Measure = IF(MAX(Trans[CourseDateTime])>MAX(Score[Startdate])&& MAX(Trans[CourseDateTime])<MAX(Score[Enddate]),MAX(Score[Bonus]),MAX(Score[Default]))
     
    Let me know if that helps. :)
    • Hariharan_R's avatar
      Hariharan_R
      Solution Sage
      7 years ago

      It is not giving the expected result. 

       

      Score table's year and Trans table's year should be considered and the value is based on the student logs in. if student B logs in then subject S2 only considered on the Trans table new columns.

       

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    Create this calculated column formula in the Trans Table

    =IF(CALCULATE(COUNTROWS(Score),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime])))>0,CALCULATE(SUM(Score[Bonus]),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime]))),CALCULATE(SUM(Score[Default]),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime]))))

    Hope this helps.

    • Hariharan_R's avatar
      Hariharan_R
      Solution Sage
      7 years ago

      It is not giving the expected result. 

       

      Score table's year and Trans table's year should be considered and the value is based on the student logs in. if student B logsin then subject S2 only considered on the Trans table new columns.

       

      Your script showing below result.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

        Share data from both tables such that i can paste those in an Excel file.  Also, show the exact result you are expecting.