Power BI Dax help

[Anonymous]

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-01' )
INSERT INTO #Temp1 VALUES ( 2, '2020-12-01' )
INSERT INTO #Temp1 VALUES ( 3, '2020-11-01' )
INSERT INTO #Temp1 VALUES ( 4, '2020-11-01' )

INSERT INTO #Temp2 VALUES ( 1, '2020-11-01', 123 )
INSERT INTO #Temp2 VALUES ( 2, '2020-11-01', 234 )
INSERT INTO #Temp2 VALUES ( 3, '2020-11-01', 587 )

SELECT	*
FROM	#Temp1

SELECT	*
FROM	#Temp2

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

DROP TABLE #Temp1
DROP TABLE #Temp2

How can I apply where clause filter to my #Temp2 using Dax New column/New Measure?
Where clause filter values are coming from Hierarctical Slicer visualization.

Need help, thanks in advance.

Thank You

Regards,

gk03

[Anonymous]

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]

@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]

@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.