Drilldown query from SQL to DAX

Question

I have been thinking about moving some drilldown queries from T-SQL to DAX to improve performance. I have not really used DAX that much as a query language and was struggling to find examples that would let me convert the following simple SQL query. I have managed to find examples of filtering tables and joining tables but no real examples of combining these operations together in DAX.

The following query that I want to convert contains joining and filtering tables. Is it possible to rewrite this in DAX?

Another concern I have is that the column fp.[Fact Product Id] has a very high cardinality, really a degenerate dimension. When I tried rewriting this in MDX to run against the tabular cube, the query takes a long time and eventually blows up with out of memory errors. Will a DAX query suffer from the same problems?

Select dcpss.[Customer Product Status Rollup],
    dcpss.[Customer Product Status],
    dc.[Customer Id],
    dc.[Customer Name],
    dcps.[Customer Product Source],
    dp.Country,
    dp.[Product Number],
    dp.[Product Name],
    drbi.[Incident Number] As [Renewed By Order Number],
    drbi.[Incident Status] As [Renewed By Order Status],
    fp.[Fact Product Id] As [Product Id],
    fp.[Product Count],
    ded.[Date] As [Expiry Date]
From [rpt].[Fact Product] fp
    Inner Join [rpt].[Active Dim Product]  dp On dp.[Historical Product Key] = fp.[Product Key]
    Inner Join [rpt].[Active Dim Customer] dc On dc.[Historical Customer Key] = fp.[Product Customer Key]
    Inner Join [rpt].[Dim Expiry Date] ded On ded.[Expiry Date Key] = fp.[Expiry Date Key]
    Inner Join [rpt].[Dim Customer Product Source] dcps On dcps.[Customer Product Source Key] = fp.[Customer Product Source Key]
    Inner Join [rpt].[Dim Customer Product Status] dcpss On dcpss.[Customer Product Status Key] = fp.[Customer Product Status Key]
    Inner Join [rpt].[Dim Renewed By Incident] drbi On drbi.[Renewed By Incident Key] = fp.[Renewed By Incident Key]
Where ded.[Month of Year Name] = 'June'
    And ded.[Year Name] = 2015
    And (ded.[Day of Month] = @DayOfMonth Or @DayOfMonth Is Null)
    And dcps.[Customer Product Source] <> 'Free PP Product'
    And dc.[Payment Type] <> 'Free'
    And dp.Country = 'AU'
    And dp.[Sales Type] <> 'Trial';

jeremyh jeremyh · Accepted Answer · 2015-07-05T21:52:25

OK I finally worked this one out. I'm sure the query can probably be written better but using this article here on projection and the article here on filtering showed how you can just use Summarize and the CalculateTable function.

Please note that this returns the distinct rows (no duplicates) which isn't a problem here as I am including the primary key field [Fact Product Id].

Evaluate
CalculateTable (
    Summarize(
        'Transactions',
        'Customer Product Status'[Customer Product Status Rollup],
        'Customer Product Status'[Customer Product Status],
        'Active Customer'[Customer Id],
        'Active Customer'[Customer Name],
        'Customer Product Source'[Customer Product Source],
        'Active Product'[Country],
        'Active Product'[Product Number],
        'Active Product'[Product Name],
        'Renewed by Order'[Incident Number],
        'Renewed by Order'[Incident Status],
        'Transactions'[Fact Product Id],
        'Transactions'[Product Count],
        'Expiry Date'[Date]
    ),
    'Expiry Date'[Month of Year Name] = "June",
    'Expiry Date'[Year Name] = "2015",
    'Expiry Date'[Day of Month] = Blank() || IsBlank(Blank()),
    'Customer Product Source'[Customer Product Source] <> "Free PP Product",
    'Active Customer'[Payment Type] <> "Free",
    'Active Product'[Country] = "AU",
    'Active Product'[Sales Type] <> "Trial"
)

Drilldown query from SQL to DAX

1 Answers