Help: brute force translating weird SQL-like language into DAX

[RMDNA]

Hi all,

I'm migrating a client's dashboards off of another vendor's platform into Power BI. However, all the calculations are handled in this weird SQL-like language in the backend. I'm not too great at nesting, so could you help me translate a couple into DAX? I can use it as a benchmark for the rest. I've pre-formatted it for readability.

Spoiler

COUNT(tracking number) where OnTimeRateDate <>(empty value)
and Date (calldate)between _start of date selection and _end of date selection
and Status in (INVOICED, COMPLETED)
and Extended Status in ((empty value),CONFIRMED, PENDING CONFIRMATION, COMPLETED)
and IsScheduledDateChangeFlag=1), 0)
/
COUNT(tracking number) where Date (calldate)between _start of date selectionand _end of date selection
and Statusin (INVOICED, COMPLETED)
and Extended Status in ((empty value),CONFIRMED, PENDING CONFIRMATION, COMPLETED)
and IsScheduledDateChangeFlag=1 )

The true extent of the insanity is something like this:

Spoiler

select ifnull((select #of WO where  Status in (INVOICED, COMPLETED)
and Extended Status in ((empty value), CONFIRMED, PENDING CONFIRMATION,COMPLETED)
and IsCreatedCompleted=0),0)
+
ifnull((select SDCD - Timeline Grade where Status in (INVOICED, COMPLETED)
and Extended Status in ((empty value), CONFIRMED, PENDING CONFIRMATION,COMPLETED)),0)
+
ifnull((select #of WO where Status in (INVOICED, COMPLETED) and Extended Status in ((empty value), CONFIRMED, PENDING CONFIRMATION,COMPLETED) and IsScheduledDateChangeFlag=1),0)
+
ifnull((select ((ifnull((SELECT COUNT(Tracking#) where IsCompleted <>0 and Date (workorders-completiondate) between _start of date selection and _end of date selection), 0))
+
(ifnull((SELECT COUNT(Tracking#) where IsExpired <>0 and Date (completiondate)between _start of date selection and _end of date selection), 0)))),0)

I understand that it's mostly just a crazy string of AND()s in series, but getting the DAX syntax down is driving me nuts. If someone could translate these two, I can repurpose pieces of it in all the other calcs.

Thanks!

[v-juanli-msft]

Hi @RMDNA 

transforms from 

COUNT(tracking number) where OnTimeRateDate <>(empty value)
and Date (calldate)between _start of date selection and _end of date selection
and Status in (INVOICED, COMPLETED)
and Extended Status in ((empty value),CONFIRMED, PENDING CONFIRMATION, COMPLETED)
and IsScheduledDateChangeFlag=1), 0)

[tracking number],[OnTimeRateDate],[calldate],[Status],Extended Status],[IsScheduledDateChangeFlag]

these are considered as columns in one table, (is my understanding right?)

"INVOICED","CONFIRMED" ...

are considered as text string,    (right?)

then i create a measure in Power BI Desktop as below,

Please Try this firstly, if it doesn't work, please let me know more details

Measure =
CALCULATE (
    COUNT[tracking number],
    FILTER (
        ALL ( tablename ),
        MAX([OnTimeRateDate]) <> BLANK ()
            && MAX([calldate]) <= [measure of _end of date selection]
            && MAX([calldate]) >= [measure of _start of date selection]
            && MAX([Status]) IN { "INVOICED", "COMPLETED" }
            && MAX([Extended Status]) IN { " ", "CONFIRMED", "PENDING CONFIRMATION", "COMPLETED" }
            && MAX([IsScheduledDateChangeFlag]) = 1
    )
)