GROUP BY CUBE()的DAX等效项

Question

我正在使用汇总(...ROLLUP...)在DAX中，它与T-SQL中的ROLLUP()实现了与GROUP相同的功能，到目前为止还不错。

是否有与GROUP BY CUBE()相同的DAX语句？我还想得到rollup的所有其他排列。

这里有一个模拟的例子，希望它能有意义

 EVALUATE SUMMARIZE(Invoices , ROLLUP( [Customer], [Product], [Year]) ,
 "Orders", DISTINCTCOUNT(Invoices[Order ID]) )

将返回这些段

Customer Product        Year
NULL     NULL           NULL
Smith    NULL           NULL
Smith    Bikes          NULL
Smith    Bikes          2015
Smith    Bikes          2016
Smith    Treadmills     NULL
Smith    Treadmills     2015
Smith    Treadmills     2016

但我想看到的是

Customer Product        Year
NULL     NULL           NULL
Smith    NULL           NULL
NULL     Bikes          NULL
Smith    Bikes          NULL
NULL     Bikes          2015
NULL     Bikes          2016
NULL     Treadmills     2015
NULL     Treadmills     2016
NULL     NULL           2015
NULL     NULL           2016
Smith    Bikes          2015
Smith    Bikes          2016
NULL     Treadmills     NULL
Smith    Treadmills     NULL
Smith    Treadmills     2015
Smith    Treadmills     2016

谢谢。

Answer 1

您可以使用ROLLUP多次生成三个不同的表，以模拟多维数据集的粒度。

ROLLUP ( [Customer], [Product], [Year] )

ROLLUP ( [Product], [Year], [Customer] )

ROLLUP ( [Year], [Customer], [Product] )

虽然这会生成三个表，但有必要对它们进行UNION并从最终的表中删除重复项，因为它会重复一些汇总和小计行，因此您必须使用DISTINCT函数。

最终的DAX表达式应如下所示：

EVALUATE
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            SUMMARIZE (
                Invoices,
                ROLLUP ( [Customer], [Product], [Year] ),
                "Orders", DISTINCTCOUNT ( Invoices[Order ID] )
            ),
            "Customer", [Customer],
            "Product", [Product],
            "Year", [Year],
            "Orders", [Orders]
        ),
        SELECTCOLUMNS (
            SUMMARIZE (
                Invoices,
                ROLLUP ( [Product], [Year], [Customer] ),
                "Orders", DISTINCTCOUNT ( Invoices[Order ID] )
            ),
            "Customer", [Customer],
            "Product", [Product],
            "Year", [Year],
            "Orders", [Orders]
        ),
        SELECTCOLUMNS (
            SUMMARIZE (
                Invoices,
                ROLLUP ( [Year], [Customer], [Product] ),
                "Orders", DISTINCTCOUNT ( Invoices[Order ID] )
            ),
            "Customer", [Customer],
            "Product", [Product],
            "Year", [Year],
            "Orders", [Orders]
        )
    )
)

最终结果应该是这样的：

Customer    Product     Year    Orders
 Smith       Bikes      2015      2
 Smith       Bikes      2016      2
 Smith     Treadmills    2016     1
 Smith       Bikes                4
 Smith     Treadmills             1
 Smith                            5
                                  5
             Bikes      2015      2
             Bikes      2016      2
           Treadmills    2016     1
             Bikes                4
           Treadmills             1
 Smith                  2015      2
 Smith                  2016      3
                        2015      2
                        2016      3

如果这有帮助，请告诉我。