Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So my last question about the World Cup was maybe too involved so i'll try breaking it up into smaller pieces.
I have 2 table visualizations:
Where one is just a subset of the other and those visualizations are based off a dataset that looks like this:
To describe it, each group has 12 rows, 3 rows for every team, 1 row for each game played by that team.
Now, I'm trying to calculate the rank of different bits of that data with:
RANKX(ALL(Scores[Team]),
CALCULATE(SUM(Scores[Goal Differential]))
)
The way I understand what is going on here is I give RANKX a list of all 32 teams. Rankx iterates over that list, applying some sort of filter to the overall scores table based on where it is in that iteration. So if the first team in the list given to RANKX is FRANCE,
CALCULATE will see this table
And correctly calculate the goal differential of France to be -3. Then of course doing that for everything else in the ALL(Scores[Team]) list, determining Rank, and spitting out a number. And it kind of works:
You can see the order of the RANK is correct in the "Phase1 GD Ranking" column but since part of RANKX's schtick is that BLANK()'s evaluate to 0, my negative numbers rank below all the other guys that aren't apart of this group.
So i thought ok, I'll just give RANKX a subset of ALL() that I care about. That way I can only get #'s 1-4. VALUES() reads like it does exactly what ALL() does except it takes into account filters so I tested it out:
Pretty confident VALUES() is returning exactly what I expect it to. Just the 4 teams that I want to rank together. So if i replace ALL(Scores[Team]) with VALUES(Scores[Team]) My brain is telling me that it should just limit the number of rankings possible, the number of iterations, etc...but what actually happens is this:
Phase 1 GD Ranking returns all 1's, While the "EXAMPLE PHASE1 GD RANK" uses ALL() and gets a lot of 0's. So...I'm guessing the table, filtered for group D passes along that team context there as well for each row, so what ends up happening is every team is ranked against only itself and therefore returns 1. Somewhere between these 2 functions is the answer I'm looking for.
Now, 1 answer that does appear to "Work" but probably only in a visual context, is capturing all the BLANKS() from filtered out teams and giving them a deeply negative number before RANKX converts them to 0 like so:
EXAMPLE3 =
RANKX(ALL(Scores[Team]),
IF(
ISBLANK(CALCULATE(
SUM(
Scores[Goal Differential]
)
)),
-10000,
CALCULATE(
SUM(
Scores[Goal Differential]
)
)
)
)
As EXAMPLE 3 column shows where the other 2 methods fail, this one passes. But it feels hacky. And while I'm not above hacky, it also feels like i'm missing an important concept and just sort of bridging around it.
Anyway TL;DR - Somewhere between ALL() and VALUES() I need a trimmed down column of distinct values based on the filtered for group. But it needs to ignore context from the team that's being "Ranked" so that the filtered table doesn't end up becoming a table of 1. ALL() returns 32 as expected. VALUES() when filtered for a group returns 4 as expected. But without being able to step 3 a dax expression I can only infer that the VALUES() gets further filtered by the context of the row being ranked. Replacing Values() with allselected is effectively the same as all. Replacing values() with allexcept(Scores, Scores[Teams]) give me the whole 98 row table, which i definitely don't want.
Solved! Go to Solution.
Modified your measure to this:
Phase1_GD_Ranking =
IF (
HASONEVALUE ( Scores[Team] ),
RANKX (
ALLSELECTED ( Scores[Group], Scores[Team] ),
CALCULATE ( SUM ( Scores[Goal Differential] ) ),
,
DESC,
SKIP
),
BLANK ()
)
Is this your desired result?
It appears so! Just reversed, but that's probably an easy fix if you used rankx.
how?
I'm literally on the edge of my seat here. I've been banging my head against the wall for 3 days.
Modified your measure to this:
Phase1_GD_Ranking =
IF (
HASONEVALUE ( Scores[Team] ),
RANKX (
ALLSELECTED ( Scores[Group], Scores[Team] ),
CALCULATE ( SUM ( Scores[Goal Differential] ) ),
,
DESC,
SKIP
),
BLANK ()
)
So i can work with this. Thank you so much!
But if i could indulge you for one more question.
This result returns this for the whole table:
Which is expected. How can i make a similar but different expression so i get the 1-4's in the goal differential column on the full table. Basically just stack all those results on top of each other?
Can you please show what would the end result look like for the statement "How can i make a similar but different expression so i get the 1-4's in the goal differential column on the full table. Basically just stack all those results on top of each other?"
So i've updated and uploaded the PBIX that i'm working with right now. Taking after the pattern that you showed me, but also adding my VAR _group = SelectedValue(Scores[Group]) filter in the calculate.
https://drive.google.com/file/d/1Rl7QuiEjyoJ282zdYb_X7YHZmTYRjZ8K/view?usp=sharing
I hope I laid it out in a way that makes sense so you can see the issue i'm currently having.
I moved group F to the bottom of the page as an example. In the filters pane you can adjust the "rounds" that are in the calculation and the "Qualified" column updates accurately. As well the "Qualified After ..." work according to plan. When filtered down to a group.
But if i try to total up the number of qualified teams I get:
Which may just be a ranking order issue except if i switch from ASC to DESC I get
Which is unfortunately, backwards. Now instead of checking for 1's and 2's for qualification I could check for 3's and 4's, but i'm worried it points to a larger issue in how I'm calculating.
Yeah if i change the measure you gave me a smidge:
I can get this. Which is almost right just like before except 30,31,32 has a change to show up where 2-3-4 are supposed to be. If i filter that full table down to one group its fine.
Can you post the pbix file as well?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |