Search code examples
powerbidaxdaxstudio

Use value from SUMMARIZECOLUMNS as filter in CALCULATE

I'm trying to use SUMMARIZECOLUMNS instead of SUMMARIZE as recommenced by this article: https://www.sqlbi.com/articles/introducing-summarizecolumns/

As shown by the DAX query below I'm trying to create a summarized table of customers and their revenue. And it works as long as I use TODAY() as the start date in DATESINPERIOD. However I want to use the value found in "'poc vDim_Customer'[DSE first conversion date]" as the start date.

If I replace TODAY() with 'poc vDim_Customer'[DSE first conversion date] I get an error indicating that it is receiving a table with multiple values.

How do I do this?

I've tried adding it as a variable outside and inside of the CALCULATE but to no avail.

Am I doing this all wrong? Should I wrap the SUMMARIZECOLUMNS in an ADDCOLUMNS or CALCULATETABLE? Still very new to this :)

Ignore that I haven't completed the average calculation yet, for starters I need to fix the dates.

    EVALUATE
SUMMARIZECOLUMNS (
    'poc vDim_Customer'[Customer_ID],
    'poc vDim_Customer'[Name],
    'poc vDim_Customer'[CountryCode],
    'poc vDim_Customer'[SAP_Channel],
    'poc vDim_Customer'[DSE first conversion date],
    KEEPFILTERS ( TREATAS ( { TRUE }, 'poc vDim_Customer'[DSE Account] ) ),
    "Avg rev 6MB",
        VAR rev =
            CALCULATE (
                [HI & Acc Rev],
                FILTER (
                    'poc vFact_SalesLine',
                    'poc vFact_SalesLine'[OrderDate_ID]
                        IN DATESINPERIOD ( 'Date'[Date], TODAY (), -6, MONTH )
                )
            )
        RETURN
            rev,
            
)
ORDER BY
    'poc vDim_Customer'[Customer_ID] ASC,
    'poc vDim_Customer'[Name] ASC,
    'poc vDim_Customer'[CountryCode] ASC,
    'poc vDim_Customer'[SAP_Channel] ASC,
    'poc vDim_Customer'[DSE first conversion date] ASC

enter image description here

Solution

  • I actually just found out what to do.

    SUMMARIZECOLUMNS lacks the row context, therefore I was getting a table and not just a single value. I was able to fix it by saving the value in a variable using VALUES('poc vDim_Customer'[DSE first conversion date])

    found the explanation of the missing row context here: https://www.sqlbi.com/articles/introducing-summarizecolumns/

    like so:

    /* START QUERY BUILDER */

EVALUATE
SUMMARIZECOLUMNS (
    'poc vDim_Customer'[Customer_ID],
    'poc vDim_Customer'[Name],
    'poc vDim_Customer'[CountryCode],
    'poc vDim_Customer'[SAP_Channel],
    'poc vDim_Customer'[DSE first conversion date],
    KEEPFILTERS ( TREATAS ( { TRUE }, 'poc vDim_Customer'[DSE Account] ) ),
    "Avg rev 6MB",
        VAR start_date =
            VALUES ( 'poc vDim_Customer'[DSE first conversion date] )
        VAR rev =
            CALCULATE (
                [HI & Acc Rev],
                FILTER (
                    'poc vFact_SalesLine',
                    'poc vFact_SalesLine'[OrderDate_ID]
                        IN DATESINPERIOD ( 'Date'[Date], start_date, -6, MONTH )
                )
            )
        RETURN
            rev
)
ORDER BY
    'poc vDim_Customer'[Customer_ID] ASC,
    'poc vDim_Customer'[Name] ASC,
    'poc vDim_Customer'[CountryCode] ASC,
    'poc vDim_Customer'[SAP_Channel] ASC,
    'poc vDim_Customer'[DSE first conversion date] ASC
/* END QUERY BUILDER */