RANKX with ASC Resuts

[sezerunar]

Hi,

I have a problem with RANKX with ASC method. To put my question in a nutshell, the result of the measure is very absurd. For example, while there are 100 observations in total for the criteria I chose, the result is 5000.

Which tables I have:

bio_data[unique, league, season, template_position]

def_data[unique, def_label, def_value]

Info:

"unique" is the key value connecting both tables. def_data has a long format. So that means there is one-to-many relationship between bio_data and def_data.

Purpose:

I want to measure the number of observations and rank according to the criteria chosen by the user. Actually, both of my measurements work properly. For example:

SELECTED PLAYER NROW =

VAR SelectedSpecialId = SELECTEDVALUE(bio_data[unique])

VAR SelectedLeague = SELECTEDVALUE(bio_data[league])

VAR PositionFilter = SELECTEDVALUE(YES_NO_TABLE[Filter]) = "Yes"

VAR DefoultPosition = SELECTEDVALUE(bio_data[template_position])

VAR SelectedPositions = ALLSELECTED(POSITION_ORDER[template_position])

RETURN

COUNTROWS(

    FILTER(ALL(bio_data),

    bio_data[league] = SelectedLeague && 

        (IF(PositionFilter, bio_data[template_position] = DefoultPosition, bio_data[template_position] IN SelectedPositions) || bio_data[unique] = SelectedSpecialId))

)

YES_NO_TABLE or POSITION_ORDER doesn't make you confused.  Depending on the user's choices, the total number of observations left in bio_data is properly revealed. For example, if the user has selected Leo Messi, how many players are there in his position, in the league he plays in. It gives me the result of this.

We have calculated the number of observations. Now let's come to RANK.

SELECTED RANK DEF =

VAR SelectedSpecialId = SELECTEDVALUE(bio_data[unique])

VAR SelectedLeague = SELECTEDVALUE(bio_data[league])

VAR PositionFilter = SELECTEDVALUE(YES_NO_TABLE[Filter]) = "Yes"

VAR DefoultPosition = SELECTEDVALUE(bio_data[template_position])

VAR SelectedPositions = ALLSELECTED(POSITION_ORDER[template_position])

VAR CurrentDefLabel = SELECTEDVALUE(def_data[def_label])

VAR FictionalTable =

CALCULATETABLE(

    VALUES(bio_data[unique]),

    ALL(bio_data),

    bio_data[league] = SelectedLeague &&

        (IF(PositionFilter, bio_data[template_position] = DefoultPosition, bio_data[template_position] IN SelectedPositions) || bio_data[unique] = SelectedSpecialId)

)

VAR UniqueObservations = FictionalTable

RETURN

RANKX (

    FILTER(

        ALLEXCEPT(def_data, def_data[def_label]),

        (

            def_data[unique] IN UniqueObservations

        )

    ),

    CALCULATE ( SUM(def_data[def_value]),

        (

        def_data[unique] IN UniqueObservations

        )

    ),

    ,

    DESC,

    Skip

)

The above formula works beautifully. However, when I make the sort method ASC instead of DESC, the results are absurd as I emphasized at the beginning. As a result of the criteria chosen by the user, maybe 100 players remain. But how can the result of RANKX be 5000 (just an example)?

Could you help me, with what I miss? 

Hope I explained well.

[sezerunar · Accepted solution]

Thanks for the answers. I finally figured it out. 

SELECTED RANK DEF =

VAR SelectedSpecialId = SELECTEDVALUE(bio_data[unique])

VAR SelectedLeague = SELECTEDVALUE(bio_data[league])

VAR PositionFilter = SELECTEDVALUE(YES_NO_TABLE[Filter]) = "Yes"

VAR DefoultPosition = SELECTEDVALUE(bio_data[template_position])

VAR SelectedPositions = ALLSELECTED(POSITION_ORDER[template_position])

VAR CurrentDefLabel = SELECTEDVALUE(def_data[def_label])

VAR FictionalTable =

CALCULATETABLE(

    VALUES(bio_data[unique]),

    ALL(bio_data),

    bio_data[league] = SelectedLeague &&

        (IF(PositionFilter, bio_data[template_position] = DefoultPosition, bio_data[template_position] IN SelectedPositions) || bio_data[unique] = SelectedSpecialId)

)

VAR UniqueObservations = FictionalTable

RETURN

RANKX (

    FILTER(

        ALLEXCEPT(def_data, def_data[def_label], def_data[def_value])
),

        (

            def_data[unique] IN UniqueObservations

        )

    ),

    CALCULATE ( SUM(def_data[def_value]),

        (

        def_data[unique] IN UniqueObservations

        )

    ),

    ,

    DESC,

    Skip

)

When I added "wyscout_def_data[def_value]" in ALLEXCEPT, the problem disappered.

[BeaBF]

@sezerunar Hi!
Try with this adjusted measure:

VAR SelectedSpecialId = SELECTEDVALUE(bio_data[unique])
VAR SelectedLeague = SELECTEDVALUE(bio_data[league])

VAR PositionFilter = SELECTEDVALUE(YES_NO_TABLE[Filter]) = "Yes"
VAR DefoultPosition = SELECTEDVALUE(bio_data[template_position])
VAR SelectedPositions = ALLSELECTED(POSITION_ORDER[template_position])

VAR CurrentDefLabel = SELECTEDVALUE(def_data[def_label])

VAR FictionalTable =
CALCULATETABLE(
VALUES(bio_data[unique]),
ALL(bio_data),
bio_data[league] = SelectedLeague &&
(IF(PositionFilter, bio_data[template_position] = DefoultPosition, bio_data[template_position] IN SelectedPositions) || bio_data[unique] = SelectedSpecialId)
)

VAR UniqueObservations = FictionalTable

VAR TotalPlayers = COUNTROWS(FictionalTable)

RETURN
TotalPlayers + 1 - RANKX (
FILTER(
ALLEXCEPT(def_data, def_data[def_label]),
(
def_data[unique] IN UniqueObservations
)
),
CALCULATE ( SUM(def_data[def_value]),
(
def_data[unique] IN UniqueObservations
)
),
,
DESC
)

BBF

[Sahir_Maharaj]

Hello @sezerunar,

Here's a couple of things to consider and troubleshooting steps you can take:

• Given that def_data is in long format, ensure that there aren't any duplicate entries for a given unique and def_label combination.
• You are using ALLEXCEPT(def_data, def_data[def_label]). This means that you are ranking over all values of def_data except for the ones associated with the currently selected def_label. If there are many other columns in def_data not related to your desired ranking, this could result in a huge ranking number.
• If you have DAX Studio, you can use the EVALUATE function to look at the intermediate table results (This can help you inspect the FictionalTable and the filtered table you pass into RANKX to ensure they're what you expect)

Should you require any further assistance, please do not hesitate to reach out to me.