Tabular - DAX - SUMMARIZE and RANKX

According to the book "Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Model" (pages 276 and 277) the following DAX query should work. However, I'll get a rank of 1 returned for every product name. My impression is that ALL doesn't block the context transition for product name. Because there is nothing in the errata, I might do something wrong!? The second query is just a workaround giving the correct result. Maybe someone can help me with the SUMMARIZE version.

EVALUATE
CALCULATETABLE(
    SUMMARIZE(
        'Internet Sales',
        Product[Product Name],
        "Sales", [Internet Total Sales],
        "Rank", RANKX( ALL(Product[Product Name] ), [Internet Total Sales] )
    ),
    Geography[English Country Region Name] = "United States"
)
ORDER BY [Sales] DESC
GO

EVALUATE
CALCULATETABLE(
    ADDCOLUMNS(       
        DISTINCT(Product[Product Name]),
        "Sales", [Internet Total Sales],
        "Rank", RANKX( ALL(Product[Product Name] ), [Internet Total Sales] )
    ),
    Geography[English Country Region Name] = "United States"
)
ORDER BY [Sales] DESC
GO

September 2nd, 2015 3:51pm

I think the issue here is because you are driving the summarization off the internet sales table and while the ALL() removes the filter context within the row context of Internet Sales there is only one Product for a given row (I think). Why are you favouring the summarize approach? In general I always favor AddColumns() over Summarize(). Summarize is quite a "heavy" function and I generally avoid it unless it's absolutely necessary.

If you switch the summarize to drive off the values of product name the summarize version should work

EVALUATE
CALCULATETABLE(
    SUMMARIZE
        VALUES(Product[Product Name]),
        Product[Product Name],
        "Sales", [Internet Total Sales],
        "Rank", RANKX( ALL(Product[Product Name] ), [Internet Total Sales] )
    ),
    Geography[English Country Region Name] = "United States"
)
ORDER BY [Sales] DESC

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 1:14am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics