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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

      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

        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.