dax udfs
2 Topics
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 measuresSolved604Views0likes2Comments
