Stuck on how to RANKX within Month, based on values at individual Day Times

[blake_leblanc]

How can I find the proper Rank within each month?

The FACT table looks like

I'm using the following Date table

The closest I've been able to get so far is with

[Value] = 
    SUMX(
        FACT_Table,
        FACT_Table[Value]
    )

[Rank] = 
VAR Result =
    RANKX(
        ALLEXCEPT(
            DIM_Dates,
            DIM_Dates[YearMonth]
        ),
        CALCULATE(
            [Value]
        )
    )
RETURN
    IF(
        [Value] <> BLANK(),
        Result,
        BLANK()
    )

But that seems to be ranking across the entire timeperiod of the dataset rather than within each month.

I suspect it has something to do with my incorrect use of `ALLEXCEPT(...)` within RANKX(), but I've tried several iterations now and can't seem to get what I'm after.

---

UPDATE:

Here is a screenshot of the desired outcome

[blake_leblanc]

@Greg_Deckler Okay, this is getting *MUCH* closer!

z Rank = 
VAR Result =
    RANKX(
        ALL(
            DIM_Times[Time]
        ),
        [Value],
        CALCULATE(
            [Value],
            REMOVEFILTERS(DIM_Dates[Day])
        )
    )
RETURN
    IF(
        [Value] <> BLANK(),
        Result,
        BLANK()
    )

[Greg_Deckler]

@blake_leblanc If I am understanding the requirement, try this (below). PBIX is attached below signature.

Rank = 
    VAR __Year = MAX('Table'[Year])
    VAR __Month = MAX('Table'[Month])
    VAR __Day = MAX('Table'[Day])
    VAR __Time = MAX('Table'[Time])
    VAR __TimeTable = SUMMARIZE(ALLSELECTED('Table'),[Year],[Month],[Day],[Time],"__Value",SUM([Value]))
    VAR __DayTable = SUMMARIZE(ALLSELECTED('Table'),[Year],[Month],[Day],"__Value",SUM([Value]))
    VAR __MonthTable = SUMMARIZE(ALLSELECTED('Table'),[Year],[Month],"__Value",SUM([Value]))
    VAR __YearTable = SUMMARIZE(ALLSELECTED('Table'),[Year],"__Value",SUM([Value]))
    VAR __TimeTable1 = 
        ADDCOLUMNS(
            __TimeTable,
            "__Rank", RANKX(__TimeTable,[__Value],,DESC)
        )
    VAR __DayTable1 = 
        ADDCOLUMNS(
            __DayTable,
            "__Rank", RANKX(__DayTable,[__Value],,DESC)
        )
    VAR __MonthTable1 = 
        ADDCOLUMNS(
            __MonthTable,
            "__Rank", RANKX(__MonthTable,[__Value],,DESC)
        )
    VAR __YearTable1 = 
        ADDCOLUMNS(
            __YearTable,
            "__Rank", RANKX(__YearTable,[__Value],,DESC)
        )
    VAR __Result = 
        SWITCH(TRUE(),
            ISINSCOPE('Table'[Time]), MAXX(FILTER(__TimeTable1, [Year] = __Year && [Month] = __Month && [Day] = __Day && [Time] = __Time),[__Rank]),
            ISINSCOPE('Table'[Day]), MAXX(FILTER(__DayTable1, [Year] = __Year && [Month] = __Month && [Day] = __Day),[__Rank]),
            ISINSCOPE('Table'[Month]), MAXX(FILTER(__MonthTable1, [Year] = __Year && [Month] = __Month),[__Rank]),
            ISINSCOPE('Table'[Year]), MAXX(FILTER(__YearTable1, [Year] = __Year),[__Rank]),
            BLANK()
        )
RETURN
    __Result

[blake_leblanc]

Wow, thank you @Greg_Deckler  ! Trying it out now! I never thought of working only from within the FACT_Table!

Will update with results!

[blake_leblanc]

Is it a complete disaster to say the results are wrong whenever I add a few more months?

1/15/18 6:00 PM	54,851.88
1/15/18 6:30 PM	54,705.52
1/15/18 7:00 PM	54,788.16
1/21/18 6:00 PM	54,304.88
1/21/18 7:30 PM	55,126.33
1/21/18 8:00 PM	54,765.28
2/19/18 6:00 PM	56,341.12
2/19/18 6:30 PM	56,480.52
2/19/18 7:00 PM	55,827.49
2/19/18 7:30 PM	55,953.19
2/19/18 8:00 PM	55,819.23
2/20/18 7:30 PM	56,527.00
3/5/18 6:30 PM	52,441.50
3/5/18 7:00 PM	52,773.19
3/5/18 7:30 PM	52,664.47
3/5/18 9:00 PM	52,176.83
3/6/18 6:30 AM	53,091.89
3/6/18 7:00 AM	52,400.88
4/1/18 8:30 AM	51,005.07
4/1/18 9:00 AM	51,230.75
4/1/18 9:30 AM	50,784.80
4/6/18 9:00 PM	51,310.07
4/6/18 9:30 PM	52,088.06
4/6/18 10:00 PM	51,687.92
5/2/18 11:00 AM	49,199.94
5/11/18 8:30 AM	49,150.41
5/11/18 9:00 AM	49,387.99
5/11/18 9:30 AM	50,018.71
5/25/18 6:00 PM	49,218.97
5/25/18 6:30 PM	49,131.96

I'm going to follow your lead, see if I can wrangle it into place.

Thank you for a look into some awesome DAX-fu @Greg_Deckler ! Your logic of "walking" each cell in the visual through the chain of virtual tables and rank calculations is really cool!

I'll report back with results, we've gotta be close!

(Lovingly shakes a frustrated fist at RANKX)

[Greg_Deckler · Accepted solution]

@blake_leblanc Assuming I got the requirements correct, here is an updated version along with PBIX attached again below signature.

Rank = 
    VAR __Year = MAX('Table'[Year])
    VAR __Month = MAX('Table'[Month])
    VAR __Day = MAX('Table'[Day])
    VAR __Time = MAX('Table'[Time])
    VAR __TimeTable = FILTER(SUMMARIZE(ALLSELECTED('Table'),[Year],[Month],[Day],[Time],"__Value",SUM([Value])), [Month] = __Month && [Year] = __Year)
    VAR __DayTable = FILTER(SUMMARIZE(ALLSELECTED('Table'),[Year],[Month],[Day],"__Value",SUM([Value])),[Month] = __Month && [Year] = __Year)
    VAR __MonthTable = FILTER(SUMMARIZE(ALLSELECTED('Table'),[Year],[Month],"__Value",SUM([Value])),[Year] = __Year)
    VAR __YearTable = SUMMARIZE(ALLSELECTED('Table'),[Year],"__Value",SUM([Value]))
    VAR __TimeTable1 = 
        ADDCOLUMNS(
            __TimeTable,
            "__Rank", RANKX(__TimeTable,[__Value],,DESC)
        )
    VAR __DayTable1 = 
        ADDCOLUMNS(
            __DayTable,            
            "__Rank", RANKX(__DayTable,[__Value],,DESC)
        )
    VAR __MonthTable1 = 
        ADDCOLUMNS(
            __MonthTable,
            "__Rank", RANKX(__MonthTable,[__Value],,DESC)
        )
    VAR __YearTable1 = 
        ADDCOLUMNS(
            __YearTable,
            "__Rank", RANKX(__YearTable,[__Value],,DESC)
        )
    VAR __Result = 
        SWITCH(TRUE(),
            ISINSCOPE('Table'[Time]), MAXX(FILTER(__TimeTable1, [Year] = __Year && [Month] = __Month && [Day] = __Day && [Time] = __Time),[__Rank]),
            ISINSCOPE('Table'[Day]), MAXX(FILTER(__DayTable1, [Year] = __Year && [Month] = __Month && [Day] = __Day),[__Rank]),
            ISINSCOPE('Table'[Month]), MAXX(FILTER(__MonthTable1, [Year] = __Year && [Month] = __Month),[__Rank]),
            ISINSCOPE('Table'[Year]), MAXX(FILTER(__YearTable1, [Year] = __Year),[__Rank]),
            BLANK()
        )
RETURN
    __Result

[blake_leblanc]

Wow... That's phenomenal @Greg_Deckler ! I cannot thank you enough!

I'm surprised how much we have "hold RANKX's hand" and pretty much step it through each jot and tittle manually. But that's likely my naivette talking!

Thank you for introducing me to the SUMMARIZE function. As someone in the early days of wrapping his mind around "DAX thinks in tables, and only tables", seeing how you weave in these temporary tables that are flashed in and out of existence is mind-blowing... 

AND THEN! On top of that, to see how your updated solution required "just" to wrap those virtual tables within a FILTER. That's crazy cool, @Greg_Deckler !

[Greg_Deckler]

@blake_leblanc I guess I should first check if the requirements are correct. My thought was that each item in the hierarchy received its own ranking. Is the true requirement that each time and day should only be ranked within the current month/year? Each month only ranked within the current year? If that's the case, that should absolutely be possible with some tweaking. I just need to understand the exact requirements for the rankings at each level.

[blake_leblanc]

@Greg_DecklerSorry, it's my fault, I should have been clearer!

Yes, the reqs you stated just now are spot on:

* Each time and day should only be ranked within the current month/year

* Each month only ranked within the current year

[Greg_Deckler]

@blake_leblanc Sample data would help greatly to figure this out. This might help: To *Bleep* with RANKX! - Microsoft Power BI Community

[blake_leblanc]

Thank you for the link @Greg_Deckler , reading now!

As for sample data, I'm not able to upload files right now due to restrictions/etc.

But in the event I do get a release, I don't see any way of uploading here... Unless I'm blind as a bat? ha (Only Insert/edit link, Insert/edit media, etc.)

In the meantime, is this embedded table worthless?

01/15/2018 18:00	54851.88
01/15/2018 18:30	54705.52
01/15/2018 19:00	54788.16
01/21/2018 18:00	54304.88
01/21/2018 19:30	55126.33
01/21/2018 20:00	54765.28

[Greg_Deckler]

@blake_leblanc I'll see what I can do with it. RANKX is notoriously frustrating at times.

[blake_leblanc]

If my DAX-fu undertanding is correct at this point, there needs to be an internal consistency between the fields used in the matrix visual and the table elements used throughout the measure.

So, for example... If the visual includes a hierarchy within the Rows that runs through 'DIM_Dates'[Year], 'DIM_Dates'[Month], 'DIM_Dates'[Day], and then finally 'DIM_Times'[Time], if I then attempt to "feed into" a measure the 'FACT_Table'[DateTime] field, even if it may be doing what it needs to do in the background, it has no "matching" row within the visual to assign the rank value to.

(Am I wrong in my thinking there?)

In this case, "feeding into" RankX a series of 'DIM_Times[Time]' values may not give it the right cardinality (unique values) to scan through.

Which may explain why, when the measure REMOVEFILTER() on the Day, it "aggregates" any matching times into a "single" instance. In this case 54,8 + 54,3 = 109,2, thus it gets the top rank

z Rank DAY = 
VAR Result =
    RANKX(
        ALL(
            DIM_Times[Time]
        ),
        CALCULATE(
            [CPkW],
            REMOVEFILTERS(DIM_Dates[Day])
        )
    )
RETURN
    IF(
        [CPkW] <> BLANK(),
        Result,
        BLANK()
    )

Meanwhile, using `REMOVEFILTERS( 'DIM_Dates'[Date] )` seems like it is treating each day block as its own independent ranking set.

So it seems it NEEDS the "full" unique field that has BOTH the date AND time, thereby allowing it to "see" the 1/15 6PM as separate from the 1/21 6PM and thus, give them independent rankings.

That's likely already stuff you understand, but that's what I'm currently trying to work through...

* How can I get it to "scan" a Day + Time as its own unique row and then "pass that" rank back to the Date+Time hierarchy?

* Maybe the problem is in how I've separated Date and Time into different DIM tables?

* Or maybe I just need to somehow tell it that the Date and Time fields within the FACT_Table should be treated "as one" rather than rely on a DateTime field that puts them together?

(Whew, I've got a LOT to learn... There's *GOTTA* be a way!)

Thanks again for your help @Greg_Deckler , I'll let you know if I get it working or uncover something new (at least to me).