Help Please Incorrect DAX code
Hi all, I am trying to edit an incorrect DAX code which is pulling out values as per this table: The expected values as as per the Expected Excel column. Employee Name AUD Total Daily Earnings CalculatedPay Calculation Date Applicable Payroll Date Calculated Pay (N) Expected (Excel Formula) Arsher B $327.81 $2,031.34 1/09/2013 0:00 1/09/2013 0:00 2031.34 2031.34 Arsher B $178.60 $0.00 4/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $185.73 $0.00 5/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $162.02 $0.00 6/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $228.94 $0.00 7/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $292.16 $0.00 8/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $151.95 $0.00 11/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $151.96 $0.00 12/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $151.96 $0.00 13/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $189.94 $0.00 14/09/2013 0:00 15/09/2013 0:00 1959.18 0 Arsher B $265.92 $1,959.18 15/09/2013 0:00 15/09/2013 0:00 1959.18 1959.18 Arsher B $141.82 $0.00 18/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $166.15 $0.00 19/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $271.50 $0.00 20/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $192.47 $0.00 21/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $269.46 $0.00 22/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $151.95 $0.00 25/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $151.96 $0.00 26/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $187.24 $0.00 27/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $221.65 $0.00 28/09/2013 0:00 29/09/2013 0:00 2063.78 0 Arsher B $309.58 $2,063.78 29/09/2013 0:00 29/09/2013 0:00 2063.78 2063.78 Daniel Gelat $151.96 $0.00 30/09/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $171.93 $0.00 1/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $151.95 $0.00 2/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $289.32 $0.00 5/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $333.89 $0.00 6/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $405.20 $0.00 7/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $151.96 $0.00 8/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $141.82 $0.00 9/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $187.85 $0.00 10/10/2013 0:00 13/10/2013 0:00 2167.23 0 Daniel Gelat $181.35 $0.00 11/10/2013 0:00 13/10/2013 0:00 2167.23 2167.23 Daniel Gelat $169.19 $0.00 14/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $184.08 $0.00 15/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $173.75 $0.00 18/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $247.18 $0.00 19/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $285.67 $0.00 20/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $151.96 $0.00 21/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $151.96 $0.00 22/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $151.96 $0.00 23/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $254.88 $0.00 26/10/2013 0:00 27/10/2013 0:00 2036.55 0 Daniel Gelat $265.92 $2,036.55 27/10/2013 0:00 27/10/2013 0:00 2036.55 2036.55 Daniel Gelat $202.35 $0.00 30/10/2013 0:00 10/11/2013 0:00 1928.3 0 Daniel Gelat $190.45 $0.00 31/10/2013 0:00 10/11/2013 0:00 1928.3 1928.3 The DAX that calculates the CalculatedPay column is as below. However, since some times the Calculation Date is skipped (not equal) to the Applicable Payroll Date, it outputs 0 which is not desired. CalculatedPay = IF ( [Calculation Date] < [Applicable Payroll Date], --then-- 0, --else-- VAR payrollId = [Payroll Day ID] VAR empID = [Employee_ID] RETURN CALCULATE(SUM('Ordinary Hours'[AUD Total Daily Earnings]),FILTER('Ordinary Hours',[Payroll Day ID]=payrollId && [Employee_ID]=empID)) ) I then tried to do a group by sum as below however, it repeats the column values and when I display in the viz, the aggregate sum of that column is incorrect Calculated Pay (N) = CALCULATE ( SUM ( 'Ordinary Hours'[AUD Total Daily Earnings] ), ALLEXCEPT ( 'Ordinary Hours', 'Ordinary Hours'[Employee Name], 'Ordinary Hours'[Applicable Payroll Date].[Date] ) ) The excel formula that works is: =IF(AND(E2>=D2,F2<>F3),F2,0) Of course excel can refer to cells in above and below rows but this is the DAX part (I am new to this) I am trying to figure out. Greg_Deckler suggested me to go down the index path here however, I am to new with Power BI to do it correctly. Thank you so much! Please help me with the correct code for the CalculatedPay or Calculated Pay (N) column. Many Many Thanks!with CTE statement giving error
I am pulling query from Oracle Database into Power BI that outputs this but I want it to output this I thought MIN(I.CREATIONDATE) would give date-time with earliest hour in my original query SELECT TO_CHAR(MIN(I.INCIDENTID)) AS "Incident ID", MIN(I.CREATIONDATE) AS "Creation Date", TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY') AS "Date", TRIM(MO.DOMAINUSERNAME) AS "Login ID", TRIM(M.MESSAGESUBJECT) AS "Email Subject" FROM MESSAGE M JOIN INCIDENT I ON M.MESSAGESOURCE = I.MESSAGESOURCE AND M.MESSAGEID = I.MESSAGEID AND M.MESSAGEDATE = I.MESSAGEDATE JOIN MESSAGEORIGINATOR MO ON M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID GROUP BY TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'), TRIM(MO.DOMAINUSERNAME), TRIM(M.MESSAGESUBJECT) Then I was advised to use CTE with row_number() but now I get Message=ORA-00933: SQL command not properly ended I tried many variations. How to fix this? with CTE as ( SELECT MyTable.*, row_number() over (PARTITION BY MIN(I.CREATIONDATE) ORDER BY I.CREATIONDATE) AS rn FROM MyTable ) SELECT * FROM CTE WHERE rn = 1 SELECT TO_CHAR(MIN(I.INCIDENTID)) AS "Incident ID", MIN(I.CREATIONDATE) AS "Creation Date", TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY') AS "Date", TRIM(MO.DOMAINUSERNAME) AS "Login ID", TRIM(M.MESSAGESUBJECT) AS "Email Subject" FROM MESSAGE M JOIN INCIDENT I ON M.MESSAGESOURCE = I.MESSAGESOURCE AND M.MESSAGEID = I.MESSAGEID AND M.MESSAGEDATE = I.MESSAGEDATE JOIN MESSAGEORIGINATOR MO ON M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID GROUP BY TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'), TRIM(MO.DOMAINUSERNAME), TRIM(M.MESSAGESUBJECT)
