none
DAX - Aggregating measures by multiple dimensions. RRS feed

  • Question

  • I have a pretty simple data warehouse (star schema). DimDate, DimProduct, DimCustomer, FactSalesOrders. I have created a Tabular Model with this data and have several measures in FactSalesOrders. Ultimately, I am trying to consume this data in SSRS with a DAX query. Therefore, I need to perform a "group by" function in DAX to group by several attributes from each of the Dimensions and aggregate the measures in the FactSalesOrders table. I've tried summarize, and addcolumns, but the performance is slow. 10 seconds to pull a simple "group by" DimDate.FiscalYear, DimProduct.SKU, DimCustomer.CustomerName. Analysis Services within Excel is able to slice this data in less than a second. I'm looking for some insight as to how to write the DAX query.

    Monday, February 23, 2015 4:29 PM

Answers

  • Hi Mstaples123542,

    According to your description, you want to create "group by" function in DAX query. Right?

    In DAX, the only way to achieve "group by" is using summarize, addcolumns. There's no better way the get the same result with better performance. Since you are using SSRS, I suggest you just use the DAX to get the whole dataset without any group and order, then you group and order all data in the SSRS.

    If you have any question, please feel free to ask.

    Regards,


    Simon Hou
    TechNet Community Support


    Tuesday, February 24, 2015 1:20 PM
    Moderator