Forum Discussion

diogobraga2's avatar
diogobraga2
Helper IV
5 years ago
Solved

Calculation to return value based on maximum date and maximum year

The goal is to create a dynamic calculation that will show the 'Returning Rate' for the maximum 'Date Adj' for the maxium 'Academic Year' per school per grade.  Based on this dataset the 'Return...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    To a matrix visual, i dragged the School Code anf Grade Level and then wrote these measures

    Measure = MAX('result (5)'[Academic Year])
    Measure 2 = MAX('result (5)'[Date])

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi diogobraga2,

If you mean to get the static value based on current table records groups, this is possible. You can try to use following calculated column formula:

 

Academic Year MAX =
CALCULATE (
    MAX ( 'result (5)'[Academic Year] ),
    FILTER (
        'result (5)',
        [School Code] = EARLIER ( 'result (5)'[School Code] )
            && [Grade Number] = EARLIER ( 'result (5)'[Grade Number] )
    )
)

Date MAX =
CALCULATE (
    MAX ( 'result (5)'[Date Adj] ),
    FILTER (
        'result (5)',
        [School Code] = EARLIER ( 'result (5)'[School Code] )
            && [Grade Number] = EARLIER ( 'result (5)'[Grade Number] )
    )
)

 

If you mean to create dynamic column fields based on filters, this is impossible.

Currently, you can't create a dynamic calculated column/table based filters. They are stored in different data levels and parent level data model(calculated column/table) not respond with the child level(measure, visual, filter, slicer) effects.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> virtual table of data view(measure, visual, filter, slicer)
Regards,

Xiaoxin Sheng