How to prevent RANKX from duplicating when I add columns

Question

I have a table with the following columns:

stockcode, commodity, inhouse, sales, GP, qty, ticket_number

the commodity and inhouse are unpivoted indicators whether a stockcode is commodity or non-commodity, and if it is an in-house product or non-inhouse

the stockcode column has unique values that I want to rank, but I want it to be dynamic with a slicer so I made a table with my slicer values "Sales, GP, Qty, times sold". I also created measures to sum up the sales, GP, and qty also count the ticket_number's as times item was bought.

I created the following code to rank those measures:

RANK STOCKCODE =
IF (
    SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 1,
    RANKX ( ALL ( Lines[stockcode] ), [ITEM TOTAL] ),
    IF (
        SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 2,
        RANKX ( ALL ( Lines[stockcode] ), [ITEM GP $] ),
        IF (
            SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 4,
            RANKX ( ALL ( Lines[stockcode] ), [QTY BOUGHT] ),
            IF (
                SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 5,
                RANKX ( ALL ( Lines[stockcode] ), [TIMES ITEM WAS BOUGHT] ),
                RANKX ( ALLNOBLANKROW ( Lines[stockcode] ), [ITEM TOTAL] )
            )
        )
    )
)

Then I create my table with the stockcodes column and drop in the measures along with the ranking measure, and it works great...until I drop in the commodity and inhouse/non inhouse columns (which are unique to the stockcodes). For some reason, and I can't understand the logic Power Bi is using to do this, the RANKX takes every variation of combination between commodity/noncommodity and inhouse/non inhouse into consideration when it creates ranks for the stockcodes and makes duplicates doing so.

I tried making a separate table of the stockcodes, removing duplicates and applying lookup columns to the stockcodes to ensure there were no duplicate combinations I wasn't aware of, but it still duplicates. Dense rank doesn't work. Skip doesn't work. I cannot figure out how to fix this.

Tried excluding the commodity and inhouse column into consideration with filters, hasones value, all selected, reloving the all() and just referring to the Table.

Tried this but it just ranked every stockcode as rank = 1:

RANK STOCKCODE =
IF (
    SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 1,
    RANKX ( ALLEXCEPT ( Lines, Lines[ISCOMMODITY], Lines[ISTAMCO] ), [ITEM TOTAL] ),
    IF (
        SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 2,
        RANKX ( ALLEXCEPT ( Lines, Lines[ISCOMMODITY], Lines[ISTAMCO] ), [ITEM GP $] ),
        IF (
            SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 4,
            RANKX ( ALLEXCEPT ( Lines, Lines[ISCOMMODITY], Lines[ISTAMCO] ), [QTY BOUGHT] ),
            IF (
                SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] ) = 5,
                RANKX (
                    ALLEXCEPT ( Lines, Lines[ISCOMMODITY], Lines[ISTAMCO] ),
                    [TIMES ITEM WAS BOUGHT]
                ),
                RANKX ( ALLEXCEPT ( Lines, Lines[ISCOMMODITY], Lines[ISTAMCO] ), [ITEM TOTAL] )
            )
        )
    )
)

Answer 1

Very glad you found a solution! You may gain a bit of performance and a little readability by getting rid of the nested ifs, leveraging the VAR functionality and using a switch instead:

RANK STOCKCODE =
VAR Selection =
    SELECTEDVALUE ( TOPNITEMS[TOPNVALUES] )
RETURN
    SWITCH (
        Selection,
        1, RANKX ( ALL ( STOCKCODE[stockcode] ), [ITEM TOTAL] ),
        2, RANKX ( ALL ( STOCKCODE[stockcode] ), [ITEM GP $] ),
        4, RANKX ( ALL ( STOCKCODE[stockcode] ), [QTY BOUGHT] ),
        5, RANKX ( ALL ( STOCKCODE[stockcode] ), [TIMES ITEM WAS BOUGHT] ),
        RANKX ( ALLNOBLANKROW ( STOCKCODE[stockcode] ), [ITEM TOTAL] )
    )

This way your dax only needs to calculate the selected value once. Basically the same as what you had in your post but depending on the size of your model, you may notice a boost in performance and it is certainnly a bit prettier ;)

Answer 2

Okay, I figured something out.

I created a new table with all unique stockcode values and linked it to my table with the values(sales, gp etc).

then I went to my rank code and changed the table with the values to the table with unique stockcodes.

RANK STOCKCODE = 

IF(SELECTEDVALUE(TOPNITEMS[TOPNVALUES])=1,RANKX(all(STOCKCODE[stockcode]),
[ITEM TOTAL]),
IF(SELECTEDVALUE(TOPNITEMS[TOPNVALUES])=2,RANKX(ALL(STOCKCODE[stockcode]),
[ITEM GP $]),
IF(SELECTEDVALUE(TOPNITEMS[TOPNVALUES])=4,RANKX(ALL(STOCKCODE[stockcode]),
[QTY BOUGHT]),
IF(SELECTEDVALUE(TOPNITEMS[TOPNVALUES])=5,RANKX(ALL(STOCKCODE[stockcode]),
[TIMES ITEM WAS BOUGHT]),
RANKX(ALLNOBLANKROW(STOCKCODE[stockcode]),
[ITEM TOTAL])))))

Then in the Visualizations pane, I made sure the stockcodes from the UNIQUE table where being used, not from the table with the values.

Last, I made lookup measures to drop into my visualization model instead of using the columns straight from the table with all the values. The DAX looks like this:

COMMODITY = `LOOKUPVALUE(Lines[ISCOMMODITY],Lines[stockcode],SELECTEDVALUE(STOCKCODE[stockcode]))`