Power BI DAX：实现动态多列重复性检测与布尔值计数

Great question! Your existing implementation for Scenario 1 works well for a single column, but we can adapt this logic to handle an unknown number of columns without hardcoding their names. DAX doesn’t have traditional loops, but we can use iterative functions and dynamic column selection to achieve the same result.

Core Approach #

We’ll:

1. Dynamically identify all the "Value" columns you want to check (using a naming pattern like ValueA, ValueB to filter them).
2. Iterate over each of these columns, checking if the current row and the previous two rows for that column are all "T".
3. Count how many columns meet this condition for each row.

DAX Calculated Column Code #

TrueColumnCount = 
// Step 1: Dynamically get all columns starting with "Value" (adjust filter if your naming differs)
VAR ValueColumns = 
    FILTER(
        COLUMNS('Table'),
        LEFT([Value], 5) = "Value" // Targets columns like ValueA, ValueB, ValueC
    )
// Step 2: Iterate each column and count how many pass the "last 3 are T" check
VAR ValidColumnCount = 
    SUMX(
        ValueColumns,
        VAR CurrentMonth = 'Table'[Month]
        // Get current row's value for the active column in the iteration
        VAR CurrentValue = CALCULATE(MAX('Table'[[Column]]), 'Table'[Month] = CurrentMonth)
        // Get value from the previous month for this column
        VAR PrevMonthValue = CALCULATE(MAX('Table'[[Column]]), 'Table'[Month] = CurrentMonth - 1)
        // Get value from two months prior for this column
        VAR TwoMonthsPriorValue = CALCULATE(MAX('Table'[[Column]]), 'Table'[Month] = CurrentMonth - 2)
        // Only count if we have 3 months of data, and all three values are "T"
        RETURN
            IF(
                CurrentMonth < 3,
                0, // Not enough historical data for the first two months
                IF(CurrentValue = "T" && PrevMonthValue = "T" && TwoMonthsPriorValue = "T", 1, 0)
            )
    )
RETURN ValidColumnCount

How This Works #

• Dynamic Column Selection: ValueColumns uses COLUMNS and a filter to automatically grab all relevant columns. If your column names follow a different pattern (e.g., end with "_Flag"), adjust the LEFT check to match (e.g., RIGHT([Value], 5) = "_Flag").
• Iteration with SUMX: SUMX loops through each column in ValueColumns, runs the check for that column, and sums up the 1s for columns that meet the criteria.
• Edge Case Handling: For the first two months (Month = 1 and 2), we return 0 immediately since there aren’t two prior months to check—matches your expected output of (0,0,2,1).

Verification Against Your Expected Output #

Let’s test this with your sample data:

• Month 1: Returns 0 (no prior months)
• Month 2: Returns 0 (only one prior month)
• Month 3: ValueA (T,T,T) and ValueC (T,T,T) both pass → count = 2
• Month 4: Only ValueB (T,T,T) passes → count = 1

This matches exactly what you’re looking for!

Notes #

• If your table has duplicate rows per month, replace MAX with SELECTEDVALUE (or adjust the filter to ensure you’re targeting the correct row).
• The column filter logic can be tweaked to match your actual column naming convention—just make sure it accurately selects only the columns you want to evaluate.

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