topic Power BI Dax help in DAX Commands and Tips

Power BI Dax help

Anonymous — Wed, 05 May 2021 05:46:37 GMT

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

Re: Power BI Dax help

Anonymous — Wed, 05 May 2021 06:26:23 GMT

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

Re: Power BI Dax help

amitchandak — Wed, 05 May 2021 08:15:32 GMT

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

Re: Power BI Dax help

Anonymous — Wed, 05 May 2021 09:01:10 GMT

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.