Forum Discussion
SQL to DAX conversion
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] ) ) )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 )
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]
)
)
)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 )Thank You Zubair_Muhammad!!
Hello Zubair_Muhammad,
I tried your query and it took very close to my solution.Only thing I am not getting in my output is Count of Operating Value.
All values in this column are NULL. I tried to debug, When I run subset of your code:
New Table =
SUMMARIZE (
C,
C[Start Time] ,
C[End Time],
"NoOfEvents", COUNT (D[OperatingValue] )
)
Still I am getting Null values for "NoOfEvents". I checked my data I also have data for it.
I am not able to debug further. Can you please suggest where my code is going wrong.Thanks
I believe there in no relationship between C and D.
Thats why the subset code you wrote is retruning zeros
In my code i dad mimiced this relationship using Calculate and Filter
Zubair_MuhammadThanks for the reply!!
But when I use Calculate and Filter in my above code it still gives NULL value. I am not able to figure out where I am going wrong.
Thanks
This works fine. However when i use slice then it display incorrect count because there is know relationships between the tables.
Can you please solve this issue?
Table have 20 rows and my table visualization display data only when Gap>=3(as given below) which i able to display on dashboard. Along with table visualization i wanted to display count of attendee who has Gap>=3 on Card visualization(Value should be 6) which i am unable to do.
Any help or idea is much appreciated.
Attendee Name Meetings Gap Claire Baker 1 4 Geneva Barnett 1 4 Johnnie Carpenter 1 4 Jonathan Goodwin 1 4 Marsha Robertson 1 4 Salvador Elliott 1 4
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
/* 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