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...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

I tried below DAX query but it is NOT working


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

 

Looks like above DAX is doing below SQL query:-

SELECT SUM(b.tot_amt) AS Volume FROM #Temp1 AS a LEFT OUTER JOIN #Temp2 b ON a.id = b.app_id AND YEAR(b.disb_dt) = YEAR(a.app_dt) AND MONTH(b.disb_dt) = MONTH(a.app_dt)

 

 

But my expected Volume output is 944 instead of 710

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.