Bonjour à toutes et à tous,

Me voilà bloqué sur une problématique depuis plusieurs semaines, c'est pour cela que je viens demander de l'aide à la communauté !

Alors, pour mon entreprise nous souhaitons passer sur PowerBI. Je cherche à calculer le taux de transformation des cotations en commandes. Vous trouverez ci dessous le code originel. Ma problématique arrive à la troisième étape de ce code "Match entre les commandes et les cotations" (en rouge). J'ai essayé toutes mes idées mais rien à faire !
Je sais qu'il est possible de le rentrer en direct Query ( et encore... Dans mon cas cela ne fonctionne pas...) mais je préférerais le faire en mode load pour ne pas perdre la visiblité du second onglet "tableau" en report view.

Dès lors, est-ce que l'un de vous serait capable de me "traduire" ce code en DAX ? Ou me donner des pistes ?


Merci d'avance

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/* Transformation rate of quotations into orders */
/*Lines of Orders */
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" >= 20180101

/* Lines of quotations*/
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" >= 20180101

/* 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