DAX Function for Maximum Value Calculation

Overview

This document outlines a DAX function designed to return the maximum value from a column called WARTOSC in a table named Mieszanie, based on the condition that a column called DATA is less than or equal to every DateTime entry in a separate table named DimDate.

Required Tables

1. Mieszanie: Contains columns DATA and WARTOSC.
2. DimDate: Contains a column DateTime.

Function Definition

DAX Code

MaxWartoscByDateTime = 
VAR MaxValueTable =
    SUMMARIZE(
        DimDate, 
        DimDate[DateTime], 
        "MaxWARTOSC", 
        MAXX(
            FILTER(
                Mieszanie, 
                Mieszanie[DATA] <= DimDate[DateTime]
            ), 
            Mieszanie[WARTOSC]
        )
    )
RETURN 
    SELECTCOLUMNS(MaxValueTable, "DateTime", DimDate[DateTime], "MaxWARTOSC", [MaxWARTOSC])

Function Explanation

• VAR MaxValueTable:

  • Creates a summarized table where we iterate over each DateTime value in DimDate and calculate the maximum WARTOSC from Mieszanie.
  • FILTER: Applies the condition that DATA must be less than or equal to DateTime.
  • MAXX: Calculates the maximum WARTOSC for each qualified record.

• RETURN:

  • Uses SELECTCOLUMNS to create a final table with specified columns, including the original DateTime and the computed maximum values.

Input Validation

Ensure that:

• Mieszanie table is not empty and contains valid date entries in the DATA column.
• DimDate table should contain valid date entries in the DateTime column.

Usage Example

To implement and visualize this function in Power BI:

1. Add the DAX code to a measure or calculated table, as needed.
2. Utilize the output in visualizations, such as tables or charts, to analyze the maximum values against the corresponding DateTime values.

Conclusion

This DAX function efficiently retrieves the maximum value of WARTOSC for each DateTime in DimDate, adhering to the specified condition regarding the DATA column. For further improvement in your DAX skills, consider exploring courses on the Enterprise DNA Platform that cover advanced DAX techniques and data modeling.