PowerBI中支持动态筛选的月度KPI指标DAX度量值实现诉求

Got it, let's tackle this problem directly—turning your multi-table logic into a single, robust DAX measure that handles filter context, historical monthly max calculations, and clean visualization for your line chart.

Final DAX Measure #

Here's the complete measure, with comments explaining each part. Just swap out the placeholder table/column names for your actual dataset:

KPI_Traffic_Ratio = 
VAR Current_Monthly_Total = SUM('Flight_Dataset'[起降量]) -- Replace with your fact table + traffic column
VAR Max_Historical_Monthly_Total = 
    CALCULATE(
        // Aggregate traffic by year + month, then pull the highest total for the current month
        MAXX(
            SUMMARIZE(
                'Flight_Dataset',
                'Date_Dim'[年份], -- Your date table's year field
                'Date_Dim'[月份], -- Your date table's month field
                "Monthly_Total", SUM('Flight_Dataset'[起降量])
            ),
            [Monthly_Total]
        ),
        // Keep only the month filter, ignore year/other date filters to get 20-year max
        ALLEXCEPT('Date_Dim', 'Date_Dim'[月份])
    )
RETURN
    // Safely divide, return 0 if no data exists to avoid errors
    DIVIDE(Current_Monthly_Total, Max_Historical_Monthly_Total, 0)

Key Breakdown #

Let's walk through why this works for your requirements:

1. Current_Monthly_Total: Calculates the total takeoffs/landings for the month(s) in your active filter context (e.g., filtered to a specific airport, region, or year range).
2. Max_Historical_Monthly_Total:
   • SUMMARIZE groups your data by year and month to get the total traffic for each historical monthly period.
   • CALCULATE + ALLEXCEPT ensures we only retain the current month filter from your date table, stripping out year and other date-related filters to pull the maximum monthly total across all 20 years of data.
   • MAXX grabs the highest value from the grouped monthly totals.
3. DIVIDE: Handles edge cases where there's no historical data (returns 0 instead of an error), keeping your line chart clean and functional.

Filter Compatibility #

This measure supports any filters you apply (airports, regions, aircraft types, etc.) because:

• The SUMMARIZE function inherits all non-date filters from your context.
• ALLEXCEPT only removes date filters except for the month, so the maximum is calculated for your filtered subset of data (e.g., only for a specific airport's historical monthly traffic).

Visualization Setup #

For your line chart:

• Add Date_Dim[年份] + Date_Dim[月份] to the axis (format as "YYYY-MM" for clarity).
• Use this KPI_Traffic_Ratio measure as the value.

内容的提问来源于stack exchange，提问作者Guto Lima