Forum Discussion

Fahad12's avatar
Fahad12
Frequent Visitor
3 years ago
Solved

Need help to remove aggregation from my DAX function

So I have a very simple DAX but always get stuck in why it always doesn't accept columns non aggregated columns... I always face this error whether I try to "Add Column" or "New Measure" they both don't allow me to use non aggregated columns as they are. I get this error message: "A single value for column 'X' in table 'Query1' cannot be determined. This can happen when a measure 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." 

 

my intention was to create a loop to calculate the cumulative SUM (Using SUMX) and store it in column Z calculated from column Y at every point in Column X. So the value of column Z at X = column Y at current X value + column Y at all previous X values and this calculation is done at every X point.

This is my DAX should follow this logic (Table name is Query1, columns: X,Y,Z): 

 

VAR __date = Query1[X]
VAR __sum = Query1[Y] at initial Query1[X] value
VAR _loop = GENERATESERIES(min(Query1[X]),max(Query1[X]),1)

VAR __RollingTable = ADDCOLUMNS (_loop, "Y Cumulative sum", __sum + SUMX(FILTER(_loop,Query1[X] <= EARLIER(Query1[X])), Query1[Y] at current X value))

Return
__RollingTable
Need Help

3 Replies