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
)
nish2288's avatar
nish2288
Helper I
8 years ago

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

    nish2288

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

     

    Regards,

    Mayur