Power Pivot: DAX for Same Value Sequential Count

Question

Please reference the following example table.

The actual table would be contained in PowerPivot.

There are multiple runs identified by sequential numbering.

Based on what we want to observe through filtering, each run has an associated value.

Here's simplified version of the current data:

Common Columns for all Data: Part: Uniquely defines the group. In this case, it's part or device. Run: Identifies a same test count. Value: The outcome generated from the test.

What I've been trying to add is an additional three columns:

Desired1: Same_Value_Count: This counts consecutive same values.
Desired2: Same_Max: Gives the maximum same value count.
Desired3: Same_Min: Give the minimum same value count.

This would result in the following PivotTable:

Resulting Table

I am having trouble formulating the proper DAX syntax to accomplish the two extra columns.

Keep in mind, I'd like to show the whole table as is.

I have a calculated column here called count_seq_dup:

=CALCULATE(COUNTROWS(table), ALLEXCEPT(table, table[3_Value]), EARLIER(Table[2_Run]) >= CSVsource[2_Run])

It worked perfectly for a single part, but does not work with multiple parts parts and when other filtering or slicers are applied.

I'm close, but it's not exactly what I'm looking for, and I can't figure out the syntax in DAX to get it right.

Can anyone help me?

Here's the link containing better accuracy of the data needed: dropbox.com/s/x9bajeq9tcax5uy/… — roncruiser

Marco Vos Marco Vos · Accepted Answer · 2018-09-29T08:20:35

For the Same_Value_Count, try something like this:

Same_Value_Count =
    VAR part = 'table'[1_Part]
    VAR val = 'table'[3_Value]
    VAR run = 'table'[2_Run]
    VAR tblpart = FILTER ( 'table', 'table'[1_Part] = part && 'table'[2_Run] <= run )
RETURN
    run - CALCULATE ( MAX ( 'table'[2_Run] ), FILTER ( tblpart, [3_Value] <> val ) )

This will return the maximum same value count for a part / value combination.

Max Count =
VAR part = 'table'[1_Part]
VAR val = 'table'[3_Value]
RETURN
    CALCULATE (
        MAX ( 'table'[Same_Value_Count] ),
        FILTER ( 'table', [3_Value] = val && 'table'[1_Part] = part )
    )

Power Pivot: DAX for Same Value Sequential Count

1 Answers