RANKx with OR and AND

[unarsezer]

There's a problem with PowerBI that I can't overcome. I have two tables. One of these tables is called "attacking_style". It has a long format. The reason for the long format is that there are different metrics in a column called attacking_labels and the corresponding values in a column called attacking_values.

My other table is called "final_biograpy_data". This table does not have the long format. The link between the two tables is provided by a column called special_id.

I also have a second slicer input. Let's call it "pos". The user can select one or more positions from the input. I want to share with you my four formulas below.

selected_position = VALUES(attacking_style[template_position])

Selected League = SELECTEDVALUE(final_biograpy_data[league])

MEASURE_attacking_nrow =
VAR CurrentLeague = [Selected League]
VAR SelectedPositions = ALLSELECTED(attacking_style[template_position])
VAR SelectedSpecialId = SELECTEDVALUE(final_biograpy_data[special_id])
RETURN
CALCULATE(
COUNTROWS(attacking_style),
FILTER(
ALLEXCEPT(attacking_style, attacking_style[attacking_label_name]),
attacking_style[league] = CurrentLeague &&
(attacking_style[template_position] IN SelectedPositions ||
attacking_style[special_id] = SelectedSpecialId)
),
USERELATIONSHIP(attacking_style[special_id], final_biograpy_data[special_id])
)

MEASURE_attacking_rank =
VAR CurrentLeague = [Selected League]
VAR SelectedPositions = ALLSELECTED(attacking_style[template_position])
VAR SelectedSpecialId = SELECTEDVALUE(final_biograpy_data[special_id])
RETURN
RANKX (
FILTER(
ALLEXCEPT(attacking_style, attacking_style[attacking_label_name]),
attacking_style[league] = CurrentLeague &&
(attacking_style[template_position] IN SelectedPositions ||
attacking_style[special_id] = SelectedSpecialId)
),
CALCULATE ( SUM ( attacking_style[attacking_values] ) ),
,
DESC,
Skip
)

The formulas above produce the results I want in some conditions. Let's say the user has selected a name as special_id and the position of this name is "CF". However, he did not select "CF" from the "pos" input. In this case, the measure "MEASURE_attacking_rank" is not working properly.

For example, the player with special_id Harry Kane was selected. This player's position is "CF". However, the user did not select the position "CF" from the "pos" input, but instead selected "AM". Even in this case the MEASURE_attacking_nrow measure works correctly. Because there are 93 players in the "AM" position. With Harry Kane it becomes 94 and this is shown in the table I created. But the MEASURE_attacking_rank measure is not giving me the result I want. For example, the player is 95th out of 94 in a certain type of metric. What am I doing wrong?

Let's say the user has selected Harry Kane. Harry Kane's position is "CF" and he plays in the "ENG1" league. But the user has selected the position "AM" from the "pos" input. In this case, the table should look like this.

All players data in the "ENG1" league in the "AM" position, and "Harry Kane"s data. From this table we will measure the RANK for each attacking_label_name.

Am I doing the filtering wrong?

[technolog]

The problem you're facing is that when a user selects a player with a specific special_id (like Harry Kane) and a different position from the player's actual position, the MEASURE_attacking_rank measure isn't working as expected.

The MEASURE_attacking_nrow measure is working correctly because it's counting the number of rows based on the selected league, selected positions, and the selected special_id. However, the MEASURE_attacking_rank measure is trying to rank the selected player based on the sum of attacking_values, but it's not considering the player if their position isn't in the selected positions.

To fix this, you need to adjust the filter in the MEASURE_attacking_rank measure to ensure that the selected player is always included, regardless of the selected positions.

Here's a revised version of the MEASURE_attacking_rank measure:

MEASURE_attacking_rank =
VAR CurrentLeague = [Selected League]
VAR SelectedPositions = ALLSELECTED(attacking_style[template_position])
VAR SelectedSpecialId = SELECTEDVALUE(final_biograpy_data[special_id])
RETURN
RANKX (
FILTER(
ALLEXCEPT(attacking_style, attacking_style[attacking_label_name]),
(attacking_style[league] = CurrentLeague && attacking_style[template_position] IN SelectedPositions)
|| attacking_style[special_id] = SelectedSpecialId
),
CALCULATE ( SUM ( attacking_style[attacking_values] ) ),
,
DESC,
Skip
)
The main change is in the filter condition. Previously, you were checking that either the template_position matches the selected positions OR the special_id matches the selected special_id. This would exclude the selected player if their position wasn't in the selected positions. By adjusting the filter condition, you ensure that the selected player is always included in the ranking, regardless of the selected positions.

With this change, when a user selects Harry Kane and the "AM" position, the measure will rank all players in the "ENG1" league in the "AM" position and also include Harry Kane's data, even though his position is "CF". This should give you the desired ranking for each attacking_label_name.