Forum Discussion

_google's avatar
_google
Icon for Helper I rankHelper I
8 years ago
Solved

3#DAX : FIND ROW NUMBER BASED ON CRITERIA

 

Hi Experts

 

Reaching out again for the DAX formula to arrive at calcuated formula to find the row number based on Criteria. For example Attrribute is valid if the marked as "Yes" in the source data table.

 

Required in DAX a calcuated column which will find the row number in serial where there is criteria is matched.

 

I tried with calcuate( count(), filter())) but believe my filter context is not correct.

 

 

Normal Excel formula

'=IF($C2="Yes",ROW(A2),"")

 

TypeAttributeIs Valid normarl excel ( cell reference) can we have inDAXDAX Function
AA1Yes 2 1??
AA2Yes 3 2 
AA3     
AA4     
AA5Yes 6 3 
AA6Yes 7 4 
AA7Yes 8 5 
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    _google

     

    May be you can use this Column

     

    Column =
VAR myrank =
    RANKX (
        FILTER ( TableName, TableName[Is Valid] = "Yes" ),
        TableName[Attribute],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( TableName[Is Valid] = "Yes", myrank )

     

     

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Not understanding what you are going for here but I can say that you probably need to add an Index column in Power Query to your query.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      _google

       

      May be you can use this Column

       

      Column =
VAR myrank =
    RANKX (
        FILTER ( TableName, TableName[Is Valid] = "Yes" ),
        TableName[Attribute],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( TableName[Is Valid] = "Yes", myrank )