Forum Discussion
How to write DAX measure that translates SQL Sub query to support YTD and MTD measures.
I'd appreciate if you could please assist in translating the below SQL query to DAX. The tricky part of it is that, when calculating YTD and MTD, the sub-query should append the filter "week number <= selected value from the filters pane".
I was able to find this post (which is a close match). However, the solution didn't give me the results I was expecting.
SELECT DT.WEEK_NUMBER,
RG.DIVISION_DESC,
RG.GROUP_DESC,
SUM( CASE
WHEN EmpFact.JOB_TYPE = 'P' THEN
0.33
ELSE
1
END
) NEW_HIRES
FROM [SCHEMA].[DATABASE].EMPLOYEE_FACT EmpFact
LEFT JOIN [SCHEMA].[DATABASE].DIM_DATE DT
ON DT."Date ID" = EmpFact."HR Employee System Keyed Date"
LEFT JOIN [SCHEMA].[DATABASE].DIM_GROUP RG
ON RG.DIM_GROUP_KEY = EMPFACT.DIM_GROUP_KEY
WHERE EmpFact.EVENT_TYPE IN ( 'HIRE', 'REHIRE' )
AND DT."Year" = 2022
AND RG.GROUP_DESC = 'Drivers'
AND Empfact.EMPLOYEE_ID IN (
SELECT DISTINCT
Empfact.EMPLOYEE_ID
FROM [SCHEMA].[DATABASE].DIM_DATE DT
LEFT JOIN [SCHEMA].[DATABASE].EMPLOYEE_FACT EmpFact
ON DT."Date ID" = EmpFact."HR Employee System Keyed Date"
WHERE EmpFact.EVENT_TYPE = ('TERMINATED')
AND DT."Year" = 2022
)
GROUP BY DT.WEEK_NUMBER,
RG.DIVISION_DESC,
RG.GROUP_DESC
ORDER BY 1,2,3
5 Replies
if you could please assist in translating the below SQL query to DAX.It will be more productive if you describe your issue and then use Power BI to try and solve it. Translating from one system to another is not productive most of the time.
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523lbendlin : My apologies! I should have elaborated the logic behind this query.
Due to the fact that I couldn't replicate the logic in DAX, I wrote a SQL query.
Here is the logic behind this sql code. 'Employee Fact' table is filtered to employees with the event type of 'hire' or 'rehire' to calculate the new hire termination count. The subquery further filters this data to retain only employees with event type 'terminated'. In simple terms, let's assume that an employee was hired in week 10 of the year 2022 and terminated in week 20 of that same year. This terminated new hire must only show up in week 20.
This logic has a tricky part: when a report is filtered for week 13, that sub query filters the 'Employee Fact' table from week 1 to week 13 and this employee is filtered out since he or she was terminated in week 20.
Hope this helps!
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
I struggled to come up with the DAX logic below (not sure if it's valid or if I'm missing anything?). However, it is not producing the desired results. Also, the YTD measure on top of this produce incorrect results (incorrect running totals when put next to the week number column for year 2022). Please advise.
New Hires Terms = VAR vTerms = CALCULATETABLE( SUMMARIZE( ALL(HR_FACT_EMPLOYEE_TRANSACTION), HR_FACT_EMPLOYEE_TRANSACTION[HR Employee ID] ), HR_FACT_EMPLOYEE_TRANSACTION[HR Employee Event Type] = "TERMINATED", CONFORMED_DATE_DIM[HR Report Week Number] <= SELECTEDVALUE(CONFORMED_DATE_DIM[HR Report Week Number]), CONFORMED_DATE_DIM[Year] <= SELECTEDVALUE(CONFORMED_DATE_DIM[Year]) ) VAR vTermsEmp = SUMMARIZE( vTerms, [HR Employee ID] ) VAR vNHTerms = CALCULATE( DISTINCTCOUNT( HR_FACT_EMPLOYEE_TRANSACTION[HR Employee Job Type Value] ), AND( HR_FACT_EMPLOYEE_TRANSACTION[HR Employee Event Type] IN { "HIRE", "REHIRE" }, HR_FACT_EMPLOYEE_TRANSACTION[HR Employee ID] IN vTermsEmp ) ) RETURN vNHTerms
