Forum Discussion

Camstr's avatar
Camstr
Icon for Helper I rankHelper I
2 years ago
Solved

New Measure to filter based on two parameters

Hello,

I have a database that contains school test results by class and subject. 

The database is updated with the latest results and retains all previous records.

I want to make a Power BI report where I can visualise the latest average grade by class and subject based upon the most recent test date.

I need the report to update everytime new tests are taken, these will be future unknown dates.

See attached screenshot of what I have and what I want.

I know that I need a new measure here, but I am struggling to 'FILTER(ALLEXCEPT...' across two different tables.

Thanks in advance

 

  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, Camstr 

     

    with your current date format "dd-mm-yyyy"  use below code 

    just adjust your table and column name

    latest test date = 
    min(parameter[test date])


updated grade = 
     MAXX(
        FILTER(parameter,
           parameter[test date]=[latest test date]
               ),
        parameter[average grade]
          )

     

    out put like below

     

     

     

     

    if date format like  "mm-dd-yyyy"  use below code 

    just adjust your table and column name

    latest test date = 
    max(parameter[test date])


updated grade = 
     MAXX(
        FILTER(parameter,
           parameter[test date]=[latest test date]
               ),
        parameter[average grade]
          )

     

2 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, Camstr 

     

    with your current date format "dd-mm-yyyy"  use below code 

    just adjust your table and column name

    latest test date = 
    min(parameter[test date])


updated grade = 
     MAXX(
        FILTER(parameter,
           parameter[test date]=[latest test date]
               ),
        parameter[average grade]
          )

     

    out put like below

     

     

     

     

    if date format like  "mm-dd-yyyy"  use below code 

    just adjust your table and column name

    latest test date = 
    max(parameter[test date])


updated grade = 
     MAXX(
        FILTER(parameter,
           parameter[test date]=[latest test date]
               ),
        parameter[average grade]
          )