topic Re: Converting SQL formula to DAX in DAX Commands and Tips

Converting SQL formula to DAX

ariam — Mon, 08 Jul 2024 20:45:44 GMT

I have a matrix table that is broken into 4 quarters using a 'date opened - year' filter on the page, and I need it to output a running total of values from previous years as well and exclude anything that has already been closed. This is the formula that a coworker wrote for when we had this on SQL (this section is just for Quarter 1 but he repeated it for each quarter):

-- New and Continued Case Totals
SELECT
'Total number of cases (new and continued) for current quarter' AS 'CaseType',
(SELECT COUNT(CaseNumber) FROM temp_Continued_Q1a WHERE (YEAR(DateOpened) = caseYear) AND (MONTH(DateOpened) = 1 OR MONTH(DateOpened) = 2 OR MONTH(DateOpened) = 3) AND (DateOpened <= CURDATE())) +
(SELECT COUNT(CaseNumber) FROM temp_Continued_Q1b WHERE (DateOpened < CONCAT(caseYear, '-01-01') AND DateClosed IS NULL) AND (CURDATE() >= CONCAT(caseYear, '-01-01')) OR (DateOpened < CONCAT(caseYear, '-01-01') AND DateClosed >= CONCAT(caseYear, '-01-01')) AND (CURDATE() >= CONCAT(caseYear, '-01-01'))) AS Q1

I have been trying for weeks to convert this into a DAX measure that will give me the output that I need. I tried breaking it into several measures and am able to get the first part by just doing a distinct count of the case numbers and throwing it into the matrix table, but the second part for the 'continuing' cases I cannot seem to get. I really hope someone can help me!

Re: Converting SQL formula to DAX

BeaBF — Tue, 09 Jul 2024 07:44:43 GMT

ariam Hi! Can you paste some sample data and the formula? also try to explain the expected output on the data you'll paste.

Re: Converting SQL formula to DAX

ariam — Thu, 15 Aug 2024 16:32:59 GMT

Nevermind - I figured out the problem I was having, and converting the SQL code was the wrong way to go in this case as it didn't give me my desired output. This is the code I needed:

Total Records Opened Before and Closed On or After Filtered Year (Including Null Closed Dates) = CALCULATE( COUNTROWS(YourTableName), ALL(YourTableName[YearColumn]), ( ISBLANK(YourTableName[ClosedDate]) || YEAR(YourTableName[ClosedDate]) >= SELECTEDVALUE(YourTableName[YearColumn]) ), YEAR(YourTableName[OpenedDate]) <= SELECTEDVALUE(YourTableName[YearColumn]) )