Search code examples
exceloffice365daxpowerpivot

What is Proper DAX GROUPBY Syntax In Calculated Column in Power Pivot in Excel in Office 365

The following syntax entered into a calculated column formula for table Visits provided below in powerpivot in excel (office 365 version):

=GROUPBY(  
   Visits,  
   [Patient Name],  
   "First_Visit_Date", 
   MINX(CURRENTGROUP(), Visits[Visit_Date])
 )

Yields the error: 

The expression refers to multiple columns. Multiple columns cannot be 
converted to a scalar value.

What is the correct DAX GROUPBY syntax when using PowerPivot embedded in Excel to add a calculated column "First_Visit_Date" to the simple source table Visits table depicted below to arrive at the target table also listed below:

Source Table (Visits):

Patient_Name, Visit_Date, Duration  
Sue,           8/10/2017,       60  
Sue,           8/12/2017,       20  
Sue,           8/20/2017,       15
Bill,          9/ 1/2018,       90  
Bill,         10/ 1/2018,       90  
Sally,         5/22/2016,       30  
Sally,         5/30/2016,       30

Target Table:

Patient_Name, Visit_Date, Duration, Calculated Column 1 
Sue,          8/10/2017,  60,       8/10/2017    
Sue,          8/12/2017,  20,       8/10/2017  
Sue,          8/20/2017,  15,       8/10/2017
Bill,         9/ 1/2018,  90,       9/ 1/2018
Bill,        10/ 1/2018,  90,       9/ 1/2018
Sally,        5/22/2016,  30,       5/22/2016 
Sally,        5/30/2016,  30,       5/22/2016
Solution

  • Your syntax looks OK, but what you may not realize is that GROUPBY outputs a table, not a scalar value.

    This is what your DAX returns if you enter it as a new table rather than a calculated column:

    Output

    For a calculated column you just want a DAX formula like this:

    CalcCol =
CALCULATE(
    MIN(Visits[Visit_Date]),
    ALLEXCEPT(Visits, Visits[Patient_Name])
)

    or like this:

    CalcCol =
MINX(
    FILTER(
        Visits,
        Visits[Patient_Name] = EARLIER(Visits[Patient_Name])
    ),
    Visits[Visit_Date]
)