DAX

[yashkumarbhatia]

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 

[Anonymous · Accepted solution]

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.

[123abc]

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

1	A
2
3
4	B
5
6	C
7

Expected Output:

Row Product Rank

1	A	1
2		1
3		1
4	B	2
5		2
6	C	3
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]

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

[123abc]

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

  1	Product	1
  2	Milk	1
  3	Butter	1
  4	Cheese	1
  5	Yogurt	1
  6	Product	2
  7	Muesli	2
  8	Porridge	2
  9	Product	3
  10	Banana	3

• 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!