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

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

nish2288, Can u please elaborate your issue!

  • nish2288's avatar
    nish2288
    Helper I
    8 years ago

     

    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 NameMeetingsGap
     Claire Baker14
     Geneva Barnett14
     Johnnie Carpenter14
     Jonathan Goodwin14
     Marsha Robertson14
     Salvador Elliott14
    • mwadhwani's avatar
      mwadhwani
      Kudo Kingpin
      8 years ago

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

        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.