Convert SQL query to DAX

[mtran1210]

I have this query in SQL. 

LeadTimeMetPctCurrentPeriod = COALESCE(CAST(CASE WHEN TotalOrdersLTMetPeriodToDate = 0 OR TotalOrderUnitsPeriodToDate = 0 THEN 0
ELSE 1 - (CAST(TotalOrdersLTMetPeriodToDate AS DECIMAL(19,3)) / CAST(TotalOrderUnitsPeriodToDate AS DECIMAL(19,3)))
END AS DECIMAL(19,3)), 0)

COALESCE(SUM(CASE WHEN dte.IsPeriodToDate = 'Yes' AND dte.IsCurrentPeriod = 'Yes' THEN MetLeadTime ELSE 0 END), 0) AS TotalOrdersLTMetPeriodToDate

COALESCE(SUM(CASE WHEN dte.IsPeriodToDate = 'Yes' AND dte.IsPreviousPeriod = 'Yes' THEN MetLeadTime ELSE 0 END), 0) AS TotalOrdersLTMetPreviousPeriodToDate

How do I convert this to a DAX measure?

[MFelix]

Hi @mtran1210 ,

Are this several columns on your SQL or a single column? It seems that you are making use of several columns.

In this case you would need something similar to this:

LeadTimeMetPctCurrentPeriod =
COALESCE (
    IF (
        Table.[TotalOrdersLTMetPeriodToDate] = 0
            || Table.[TotalOrderUnitsPeriodToDate] = 0,
        0,
        1
            - DIVIDE (
                ( Table.[TotalOrdersLTMetPeriodToDate] ),
                Table[TotalOrderUnitsPeriodToDate]
            )
    ),
    0
)


TotalOrdersLTMetPeriodToDate =
COALESCE (
    IF (
        Table.[IsPeriodToDate] = "Yes"
            && Table.[IsCurrentPeriod] = "Yes",
        Table[MetLeadTime],
        0
    ),
    0
)


TotalOrdersLTMetPreviousPeriodToDate =
COALESCE (
    IF (
        Table[IsPeriodToDate] = "Yes"
            && Table[IsPreviousPeriod] = "Yes",
        Table[MetLeadTime],
        0
    ),
    0
)

If we are talking about measure then this needs to be calculated in a different way.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.