Forum Discussion

RMDNA's avatar
RMDNA
Solution Sage
7 years ago

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

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!

 

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

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