Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
WHERE D.OperatingValue <= 5
AND D.ProcessTime BETWEEN C.StartTime AND C.EndTime
GROUP BY C.StartTime,
C.EndTime
Thanks in Advance!
Solved! Go to Solution.
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 )
Hello @mwadhwani,
Instead of converting the query, you can just use it Power BI.
Power BI supports native queries to SQL, so you can use SQL to query data.
Here is how where to put your query:
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] ) ) )
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
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 @nish2288,
Create a following measure using DAX:
Count Of Attendee Gap gt 3 = CALCULATE(COUNT(TableName[Attendee Name]),TableName[Gap]>3)
After creating measure pull this measure on Card Visual. You will get your solution.
Regards,
Mayur
Getting following error:
A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression.This is not allowed.
There is a twist.Gap is not a column in the table,Its a measure that i have calculated.
Suppose attendee 'John' have attended 3 meeting then there will be 3 rows in database table for 'john'.
There is predefined number of meeting that every attendee should attend for ex. 5 which i stored in different measure.
So Gap for John will be 5-3=2. Like wise for other attendee.
Create a calculated column using DAX for Gap then you may use above measure definition, it will work.
Regards,
Mayur
There is a twist. Gap is not a column in the table,its a measure that i have created.
Suppose Attendee name 'John' have attended 3 meeting then there will be 3 rows in database table for 'John'.
There is predefined value which define how many meetings everyone should attend for ex. 5.
So for 'John' gap would be 2.
Like wise for other attendee.
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
Could you share the file?
Hello @Zubair_Muhammad
I am getting NULL values for some start and End Time instead of having data.I am not able to figure out where I am going wrong.
Please find the below file:
https://www.dropbox.com/s/mo49p1ohzb5dfgo/SampleFile.pbix?dl=0
Thanks in Advance!
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |