Search code examples
daxranksummarize

DAX Difficulty counting rows and assigning a rank

I'm trying to select lines from table Cases, count the rows for each Broker in the list, and then get the rank for each Broker. My code works up to the last step. I know that because the measure that used the code up to Table 2 is giving me correct results. But I'm doing something wrong in calculating the rank. Can you tell me what the problem is? I tried using ALL(Table3), but that gave me a syntax error ("All needs a table reference, not a table expression.")

This is driving me nuts. I've wasted hours trying variations, looking for examples, reading documentation, but there's something here I just don't get. I finally got the FILTER, SELECTCOLUMNS, SUMMARIZE, and ADDCOLUMNS sequence working correctly. (Although using SUMMARIZE to add a column is apparently not recommended--but it seems to work OK here.)

BrkPricedCtRank = 
VAR Table0 = 
FILTER ( 'Cases',
    not ISBLANK( [Date To Pricing] )
    && [Date Initiated] >= date(2021,1,1)
    && [Date Initiated] <= DATE(2021,12,31)  )

VAR Table1 =
SELECTCOLUMNS( 
    Table0,  
    "Broker", [Broker]
    )

VAR Table2 = 
SUMMARIZE(
    Table1,
    [Broker], 
    "PricedCt", COUNTROWS( Table1 )  )

--For measure BrkPricedCt. the code continues with these 2 lines.
--That measure gives me what I want.
--   RETURN
--      MINX( Table2, [PricedCt] )

VAR Table3 = 
     ADDCOLUMNS( 
         Table2,
         "Rank", RANKX( Table2, [PricedCt] )  )

return MINX( Table3, [Rank] )

Here is my output: enter image description here

Solution

  • When using RANKX on Table2 and then adding it in a visual you add a filter to each row which will evaluate to 1 on all rows (all are ranked number 1 in their own row = context). But we want to have ranking for all rows which we achieve by changing to RANKX( ALL(Table2), [PricedCt] ) or RANKX( ALLSELECTED(Table2), [PricedCt] ) if you still want the ranking to be 1, 2, 3 when filtering only certain brokers.