Forum Discussion

Chandrashekar's avatar
Chandrashekar
Icon for Resolver III rankResolver III
4 years ago
Solved

indentify first instance based on multi criteria power bi

Hello,

 

How to get first instance based on multi criteria. I have shared excel formula to get and for reference.

 

Sample Data: Sample Data 

 

Regards,

Chandrashekar B

  • tamerj1's avatar
    tamerj1
    4 years ago

    Chandrashekar 
    Please refer to attached sample file. You need to addindex column using power query.

    Result = 
VAR CurrentIndex = 'Table'[Index]
VAR CurrentTable = CALCULATETABLE ( 'Table', ALL ( 'Table'[Index] ) )
VAR FilteredTable = FILTER ( CurrentTable, 'Table'[Index] <= CurrentIndex )
RETURN
    IF ( COUNTROWS ( FilteredTable ) = 1, 1, 0 )

     

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Chandrashekar 
    Please refer to attached sample file. You need to addindex column using power query.

    Result = 
VAR CurrentIndex = 'Table'[Index]
VAR CurrentTable = CALCULATETABLE ( 'Table', ALL ( 'Table'[Index] ) )
VAR FilteredTable = FILTER ( CurrentTable, 'Table'[Index] <= CurrentIndex )
RETURN
    IF ( COUNTROWS ( FilteredTable ) = 1, 1, 0 )