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
WHERE D.OperatingValue <= 5
AND D.ProcessTime BETWEEN C.StartTime AND C.EndTime
GROUP BY C.StartTime,
C.EndTime


Thanks in Advance!

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
)

20 Replies

  • Birdjo's avatar
    Birdjo
    Resolver II
    8 years ago

    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's avatar
      mwadhwani
      Kudo Kingpin
      8 years ago

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