Forum Discussion

KaterinaB's avatar
KaterinaB
Icon for Helper II rankHelper II
5 months ago
Solved

Converting existing simple measure to UDF - cannot determine a single value for column

Hi, newbie to UDFs here, so apologize upfront for the simplicity of the question.
I am trying to test how to implement this converting some simple existing measure patterns into UDFs.

We have a test dataset with some "Last Month" and "Second to Last Month" measures, with such structure

LastMonthProfiles =
VAR LastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
RETURN
CALCULATE(
SUM('Interest Profiles'[Total number of profiles]),
'Interest Profiles'[Month] = LastMonthStart
)

SecondToLastMonthProfiles =
VAR SecondtoLastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1)
RETURN
CALCULATE(
SUM('Interest Profiles'[Total number of profiles]),
'Interest Profiles'[Month] = SecondtoLastMonthStart
)

LastMonthLeads =
VAR LastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
RETURN
CALCULATE(
SUM(MQLs[Number of Leads]),
MQLs[Month] = LastMonthStart
)

SecondtoLastMonthLeads =
VAR SecondtoLastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1)
RETURN
CALCULATE(
SUM(MQLs[Number of Leads]),
MQLs[Month] = SecondtoLastMonthStart
)

I want to have UDF defining the expression, the date column and how many months into the past it should look. I first started just to simply have UDF for Last Month:

DEFINE
    FUNCTION LastMonthCalc = (ResultExpression : SCALAR EXPR, DateColumn: SCALAR DATETIME VAL) =>
        VAR LastMonth = DATE(YEAR(TODAY()), MONTH(TODAY()) -1, 1)
        RETURN
            CALCULATE(ResultExpression,
            DateColumn = LastMonth)          
        
EVALUATE
    {LastMonthCalc(SUM('Interest Profiles'[Total number of profiles]), 'Interest Profiles'[Month])}


I am getting an error: A single value for column 'Month' in table 'Interest Profiles' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result
I dont get that error in my existing measures


DAX UDFs
  • johnt75's avatar
    johnt75
    5 months ago

    The problem is the type declaration of DateColumn. The function is expecting a scalar value, but you actually want to pass in a column, as you will be applying a filter on that column. Try

    DEFINE
    FUNCTION LastMonthCalc = (
            ResultExpression : SCALAR EXPR,
            DateColumn : COLUMNREF
        ) =>
        VAR LastMonth =
            DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
        RETURN
            CALCULATE ( ResultExpression, DateColumn = LastMonth )

EVALUATE
{
    LastMonthCalc (
        SUM ( 'Interest Profiles'[Total number of profiles] ),
        'Interest Profiles'[Month]
    )
}

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    5 months ago

    The problem is the type declaration of DateColumn. The function is expecting a scalar value, but you actually want to pass in a column, as you will be applying a filter on that column. Try

    DEFINE
    FUNCTION LastMonthCalc = (
            ResultExpression : SCALAR EXPR,
            DateColumn : COLUMNREF
        ) =>
        VAR LastMonth =
            DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
        RETURN
            CALCULATE ( ResultExpression, DateColumn = LastMonth )

EVALUATE
{
    LastMonthCalc (
        SUM ( 'Interest Profiles'[Total number of profiles] ),
        'Interest Profiles'[Month]
    )
}
    • KaterinaB's avatar
      KaterinaB
      Icon for Helper II rankHelper II
      5 months ago

      Yep, that was the issue, thanks for the quick help