Skip to content
Advertisement

Power BI DAX – find repeatability

Given data as such:

Month  ValueA
1      T
2      T
3      T
4      F

Is there a way to make a measure that would find if for each month, last three Values were True? So the output would be (F,F,T,F)? That would propably mean that my actual problem is solvable, which is finding from:

Month  ValueA  ValueB  ValueC
1      T       F       T
2      T       T       T
3      T       T       T
4      F       T       F

the count of those booleans for each row, so the output would be (0,0,2[A and C],1[B])

EDIT: Okay, I managed to solve the first part with this:

Previous =
VAR PreviousDate =
    MAXX(
        FILTER(
            ALL( 'Table' ),
            EARLIER( 'Table'[Month] ) > 'Table'[Month]
        ),
        'Table'[Month]
    )
VAR PreviousDate2 =
    MAXX(
        FILTER(
            ALL( 'Table' ),
            EARLIER( 'Table'[Month] ) - 1 > 'Table'[Month]
        ),
        'Table'[Month]
    )
RETURN
    IF(
        CALCULATE(
            MAX( 'Table'[Value] ),
            FILTER(
                'Table',
                'Table'[Month] = PreviousDate
            )
        ) = "T"
            && CALCULATE(
                MAX( 'Table'[Value] ),
                FILTER(
                    'Table',
                    'Table'[Month] = PreviousDate2
                )
            ) = "T"
            && 'Table'[Value] = "T",
        TRUE,
        FALSE
    )

But is there a way to use it with unknown number of columns? Without hard – coding every column name? Like a loop or something.

Advertisement

Answer

I would redo the data table in power query (upivoting the ValueX-columns) and changing T/F to 1/0. Then have a dim table with a relationship to Month, like this: enter image description here enter image description here Then add a measure like this:

Three Consec T = 
var maxMonth = MAX('Data'[Month])
var tempTab = 
FILTER(
    dimMonth;
    'dimMonth'[MonthNumber] <= maxMonth && 'dimMonth'[MonthNumber] > maxMonth -3
)
var sumMonth =
MAXX(
    'dimMonth';
    CALCULATE(
        SUM('Data'[OneOrZero]);
        tempTab
    )
)

return
IF(
    sumMonth >= 3; 
    "3 months in a row";
    "No"
)

Then I can have a visual like this when the slicer indicates which time window I’m looking at and the table shows if there has been 3 consecutive Ts or not.

enter image description here

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement