Forum Discussion

blake_leblanc's avatar
blake_leblanc
Icon for Advocate I rankAdvocate I
3 years ago
Solved

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

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] = ...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
blake_leblanc
Icon for Advocate I rankAdvocate I
3 years ago

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 !