Forum Discussion

mwadhwani's avatar
mwadhwani
Kudo Kingpin
8 years ago
Solved

SQL to DAX conversion

Hello Experts,   I am facing issue while converting below SQL query into DAX. Can you please help me to convert the query:   SELECT COUNT(D.OperatingValue), C.StartTime, C.EndTime FROM C, D ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    mwadhwani

     

    Try this New Table from Modelling Tab

     

    New Table =
SUMMARIZE (
    C,
    C[Start Time],
    C[End Time],
    "Count", CALCULATE (
        COUNT ( D[Operating value] ),
        FILTER (
            D,
            D[Operating value] <= 5
                && D[Process Time] >= C[Start Time]
                && D[Process Time] <= C[End Time]
        )
    )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    mwadhwani

     

    Another way

     

    New Table2 =
CALCULATETABLE (
    SUMMARIZE (
        C,
        C[Start Time],
        C[End Time],
        "Count", CALCULATE (
            COUNT ( D[Operating value] ),
            FILTER ( D, D[Process Time] >= C[Start Time] && D[Process Time] <= C[End Time] )
        )
    ),
    D[Operating value] <= 5
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

mwadhwani

 

Try this New Table from Modelling Tab

 

New Table =
SUMMARIZE (
    C,
    C[Start Time],
    C[End Time],
    "Count", CALCULATE (
        COUNT ( D[Operating value] ),
        FILTER (
            D,
            D[Operating value] <= 5
                && D[Process Time] >= C[Start Time]
                && D[Process Time] <= C[End Time]
        )
    )
)
LucienF38's avatar
LucienF38
Helper I
7 years ago

Hi Zubair_Muhammad,

 

I'm sorry to interfer in topic which is not mine but I've been stuck on a big issue for a long time and my issue is similar to this one !! 

 

I have a SQL code that I want to reproduce into power BI. I've been trying all my ideas in power query editor and can't get rid of it...

 

Do you know if Power BI can recognize a SQL code ? If yes how ? Maybe in direct query ??

 

If no, does anyone can help me to "translate" the highlighted lines below ? I can't compute the "match between orders and quotation" lines.

 

Somes indications :

- CO = Orders

- Q = Quotations

 

Thanks for helping Smiley Happy 

/* Transformation rate of quotations into orders */
/* Orders lines*/
IF OBJECT_ID('tempdb..#tmp_SOL_Quot1') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot1 END
SELECT  OOLINE."ORNO - Customer order number"
                                , OOLINE."PONR - Order line number"
                                , OOLINE."ITNO - Item number"
                                , OOLINE."ORST - Highest status - customer order"
                                , OOHEAD."CUNO - Customer"
                                , OOLINE."RGDT - Entry date"
                                , CONCAT(OOLINE."ORNO - Customer order number",OOLINE."PONR - Order line number") AS "Key CO"
INTO      #tmp_SOL_Quot1
FROM   M3JDTP600."V_OOLINE - TF: CO line file (OB)" OOLINE
                                INNER JOIN M3JDTP600."V_OOHEAD - TF: CO header file (OA)" OOHEAD
                                ON OOHEAD."ORNO - Customer order number" = OOLINE."ORNO - Customer order number"
WHERE OOLINE."ORST - Highest status - customer order" > '05'
                                AND OOLINE."ORST - Highest status - customer order" < 99
                                --AND OOLINE."RGDT - Entry date" >= 20160101

/* Quotation lines */
IF OBJECT_ID('tempdb..#tmp_SOL_Quot2') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot2 END
SELECT  OOLINE."ORNO - Customer order number"
                                , OOLINE."PONR - Order line number"
                                , OOLINE."ITNO - Item number"
                                , OOLINE."ORST - Highest status - customer order"
                                , OOHEAD."CUNO - Customer"
                                , OOLINE."RGDT - Entry date"
                                , CONCAT(OOLINE."ORNO - Customer order number",OOLINE."PONR - Order line number") AS "Key Q"
INTO      #tmp_SOL_Quot2
FROM   M3JDTP600."V_OOLINE - TF: CO line file (OB)" OOLINE
                                INNER JOIN M3JDTP600."V_OOHEAD - TF: CO header file (OA)" OOHEAD
                                ON OOHEAD."ORNO - Customer order number" = OOLINE."ORNO - Customer order number"
WHERE OOLINE."ORST - Highest status - customer order" = '05'
                                --AND OOLINE."RGDT - Entry date" >= 20150101

/* Match between orders and quotations */
IF OBJECT_ID('tempdb..#tmp_SOL_Quot') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot END
SELECT  *
INTO      #tmp_SOL_Quot
FROM   (SELECT TTQ."ORNO CO"
                                                                , TTQ."PONR CO"
                                                                , TTQ."Entry date CO"
                                                                , TTQ."ORNO Q"
                                                                , TTQ."PONR Q"
                                                                , TTQ."Entry date Q"
                                                                , RANK() OVER (PARTITION BY "Entry date Q","Key Q" ORDER BY "Key Q","Entry date Q",TTQ."Entry date CO","Key CO" ASC) AS Ranking
                                                                , [Key CO]
                                FROM   (SELECT *
                                                                FROM   (SELECT #tmp_SOL_Quot1."ORNO - Customer order number" AS "ORNO CO"
                                                                                                                                , #tmp_SOL_Quot1."PONR - Order line number"                AS "PONR CO"
                                                                                                                                , #tmp_SOL_Quot1."RGDT - Entry date" As "Entry date CO"
                                                                                                                                , #tmp_SOL_Quot2."ORNO - Customer order number" AS "ORNO Q"
                                                                                                                                , #tmp_SOL_Quot2."PONR - Order line number" AS "PONR Q"
                                                                                                                                , #tmp_SOL_Quot2."RGDT - Entry date" AS "Entry date Q"
                                                                                                                                , RANK() OVER (PARTITION BY #tmp_SOL_Quot1."RGDT - Entry date","Key CO" ORDER BY "Key CO",#tmp_SOL_Quot1."RGDT - Entry date", "Key Q" ASC) AS Rooky
                                                                                                                                , [Key CO]
                                                                                                                                , [Key Q]
                                                                                                FROM   #tmp_SOL_Quot1
                                                                                                                                INNER JOIN #tmp_SOL_Quot2
                                                                                                                                ON #tmp_SOL_Quot1."ITNO - Item number" = #tmp_SOL_Quot2."ITNO - Item number"
                                                                                                                                AND #tmp_SOL_Quot1."CUNO - Customer" = #tmp_SOL_Quot2."CUNO - Customer"
                                                                                                                                AND #tmp_SOL_Quot1."RGDT - Entry date" > #tmp_SOL_Quot2."RGDT - Entry date") TTQ
                                                                WHERE TTQ."Rooky" = 1) TTQ) TTQ
WHERE "Ranking" = 1