Forum Discussion

10119166's avatar
10119166
Frequent Visitor
7 years ago
Solved

Count iterative rows meeting a specific condition

Hi I am trying to interatively count rows in PowerBI that meet a given condition.   If the values began in cell A2 in Excel, the equation starting in B2 would be: =countif($a$2:a2,a2)...  How would I do this in DAX or M?  Thank you for any help any one can provide!

 

Row      Column A   Column B

 2                    a                          1

 3                    a                          2

4                     a                          3

5                     b                         1

6                     b                         2

7                     b                         3

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    10119166  here is the dax , change column name and table name as per your data model

     

    My Count = 
VAR __r = MAX( Table[Row] )
RETURN 
CALCULATE( 
    COUNTROWS( Table ), 
    ALLEXCEPT( 
        Table, 
        Table[Column A] 
    ), 
    Table[Row] <= __r 
)

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    10119166  here is the dax , change column name and table name as per your data model

     

    My Count = 
VAR __r = MAX( Table[Row] )
RETURN 
CALCULATE( 
    COUNTROWS( Table ), 
    ALLEXCEPT( 
        Table, 
        Table[Column A] 
    ), 
    Table[Row] <= __r 
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You can use the following for a new calculated column:

    Calculated Column = 
var __CurrentRow = 'Table'[Row]
var __CurrentColumns = 'Table'[Column]
return

CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Column] = __CurrentColumns
        &&
        'Table'[Row] <= __CurrentRow
    )
)

    or the following as a measure 

    Measure = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL( 'Table'),
        MAX( 'Table'[Row] ) >= 'Table'[Row]
    ),
    VALUES( 'Table'[Column])
)
  • 10119166's avatar
    10119166
    Frequent Visitor
    7 years ago

    Thank you so much for your help!  This worked great!