Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Difference Between ALL and VALUES direction

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.

  • 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 ()
    )

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion

        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 ()
        )