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

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        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