SQL to DAX conversion

[mwadhwani]

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!

[Zubair_Muhammad · Accepted solution]

@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]
        )
    )
)

[Birdjo]

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:

[mwadhwani]

Hi @Birdjo,

Thank you @Birdjo for reply.

I have imported my data into Power BI. Also I have my source as Excel.So I cannot use above option.
So I need DAX query

Thanks

[Zubair_Muhammad · Accepted solution]

@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]

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

[nish2288]

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?

[mwadhwani]

@nish2288, Can u please elaborate your issue!

[nish2288]

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

[mwadhwani]

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

[nish2288]

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.

[mwadhwani]

[nish2288]

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.

[mwadhwani]

@nish2288

Create a calculated column using DAX for Gap then you may use above measure definition, it will work.

Regards,

Mayur

[nish2288]

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.

[mwadhwani]

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 

[Zubair_Muhammad]

@mwadhwani

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

[mwadhwani]

@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

[Zubair_Muhammad]

@mwadhwani

Could you share the file?

[mwadhwani]

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!

[mwadhwani]

Thank You @Zubair_Muhammad!!

[Zubair_Muhammad · Accepted solution]

@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
)