Forum Discussion

ariam's avatar
ariam
Regular Visitor
2 years ago
Solved

Converting SQL formula to DAX

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!

  • ariam's avatar
    ariam
    2 years ago

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

     

2 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

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

  • ariam's avatar
    ariam
    Regular Visitor
    2 years ago

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