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 ...
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
)
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]
        )
    )
)
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        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
)