Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Power BI Dax help

Hello All,   I've SQL query here:- CREATE TABLE #Temp1 ( id smallint, app_dt date ) CREATE TABLE #Temp2 ( app_id smallint, disb_dt date, tot_amt numeric ) INSERT INTO #Temp1 VALUES ( 1, '2020-11...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous ,


a new column in temp1

 

Column 1 = CALCULATE(SUM(#Temp2[tot_amt]), FILTER(ALL(#Temp1), eomonth(#Temp2[disb_dt],0) = eoMONTH(#Temp1[app_dt],0) && #Temp2[app_id]= #Temp1[id]))

 

 

this should work as a measure if they are joined on id and app_id

 

CALCULATE(SUM(#Temp2[tot_amt]), FILTER(ALL(#Temp2), MONTH(#Temp2[disb_dt]) = MONTH(#Temp1[app_dt]) && YEAR(#Temp2[disb_dt]) = YEAR(#Temp1[app_dt])))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak 

relationship is already defined at the Model between Id = app_Id.

 

So I was trying to create Measure under Table1 data_set but it is throwing me an error:-

 

A single value for column 'app_dt' in table '#Temp1' 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.