Forum Discussion

yashkumarbhatia's avatar
yashkumarbhatia
Frequent Visitor
1 year ago
Solved

DAX

We need to provide rank. The logic for the ranking is that whenever you have a value “Product” then you must start the new number and all others below should follow the same rank. Please check out the sample input and the expected output. Please provide or help me with the DAX to use 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for 123abc's concern about this issue.

     

    Hi, yashkumarbhatia 

    I am glad to help you.

     

    Based on your description, you need a ranking of the data that whenever you have a value "Product" then you must start the new number and all others below should follow the same rank.


    Maybe you can refer to my DAX.
    New Column in Power BI Desktop:

    RNK = 
VAR CurrentRow = Table1[ID]
VAR RNKVal = 
    CALCULATE(
        COUNTROWS(Table1),
        FILTER(
            Table1,
            Table1[ID] <= CurrentRow && Table1[Vals] = "Product"
        )
    )
RETURN RNKVal


    Here is the result:

     


    I have attached the corresponding pbix file below, I hope it helps you.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • 123abc's avatar
    123abc
    Community Champion
    1 year ago

    To create a ranking based on the "Product" column as per your logic in Power BI, where a new rank starts whenever "Product" appears, you can use a DAX formula to generate this custom ranking.

    Here's a sample approach:

    Sample Data (Input):

    Row Product

    1A
    2 
    3 
    4B
    5 
    6C
    7 

     

     

    Expected Output:

    Row Product Rank

    1A1
    2 1
    3 1
    4B2
    5 2
    6C3
    7 3

    DAX Solution:

    1. Create a Calculated Column in your table with this DAX formula:
    DAX
    Copy code
    Rank = VAR CurrentRow = Table[Row] VAR ProductCheck = Table[Product] RETURN IF( NOT(ISBLANK(ProductCheck)), RANKX(FILTER(Table, Table[Row] <= CurrentRow && NOT(ISBLANK(Table[Product]))), Table[Row], , ASC), CALCULATE( MAXX(FILTER(Table, Table[Row] < CurrentRow && NOT(ISBLANK(Table[Product]))), RANKX(FILTER(Table, Table[Row] <= CurrentRow && NOT(ISBLANK(Table[Product]))), Table[Row], , ASC)) ) )

     

     

     

     

    • yashkumarbhatia's avatar
      yashkumarbhatia
      Frequent Visitor
      1 year ago

      Please write the DAx in and aaranged manner and the output after writing this is please help 123abc 

       

      • 123abc's avatar
        123abc
        Community Champion
        1 year ago

        I can see that you're trying to assign a ranking to values in the "Vals" column. The requirement is to reset the rank every time a "Product" entry appears. I will provide a more detailed and organized DAX approach to achieve this.

        Here is a step-by-step guide to writing the DAX measure for this case:

        DAX Solution for Custom Ranking

        1. Create a Calculated Column using the following DAX formula:

        Rank =
        VAR CurrentRow = Table[ID]
        VAR IsProduct = Table[Vals] = "Product"
        RETURN
        IF(


        IsProduct,


        RANKX(
        FILTER(Table, Table[ID] <= CurrentRow && Table[Vals] = "Product"),
        Table[ID],
        , ASC
        ),
        CALCULATE(
        MAXX(
        FILTER(Table, Table[ID] < CurrentRow && Table[Vals] = "Product"),
        RANKX(FILTER(Table, Table[ID] <= CurrentRow && Table[Vals] = "Product"), Table[ID], , ASC)
        )
        )
        )

         

         

        Explanation:

        • CurrentRow: Holds the value of the current row's "ID".
        • IsProduct: Checks if the current row has the value "Product" in the "Vals" column.
        • If condition:
          • If the current row contains "Product", it generates a new rank using RANKX().
          • For rows that do not contain "Product", it assigns the rank of the most recent "Product" above the current row.

        • Expected Output Based on Image:

          ID Vals Rank
          1Product1
          2Milk1
          3Butter1
          4Cheese1
          5Yogurt1
          6Product2
          7Muesli2
          8Porridge2
          9Product3
          10Banana3

           

        • This approach should generate the expected output, where each "Product" starts a new rank, and all other values below it follow the same rank until a new "Product" appears.

           

        • Let me know if this resolves the issue!



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for 123abc's concern about this issue.

     

    Hi, yashkumarbhatia 

    I am glad to help you.

     

    Based on your description, you need a ranking of the data that whenever you have a value "Product" then you must start the new number and all others below should follow the same rank.


    Maybe you can refer to my DAX.
    New Column in Power BI Desktop:

    RNK = 
VAR CurrentRow = Table1[ID]
VAR RNKVal = 
    CALCULATE(
        COUNTROWS(Table1),
        FILTER(
            Table1,
            Table1[ID] <= CurrentRow && Table1[Vals] = "Product"
        )
    )
RETURN RNKVal


    Here is the result:

     


    I have attached the corresponding pbix file below, I hope it helps you.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.