Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Replicate this SQL in DAX

Hi, Please can someone help me replicate the below SQL in DAX:   SELECT      c.id AS [c.id]      , c.status AS [c.status]      , c.script_validation_id AS [c.script_validation_id]      , sv.id...
Need Help
Stachu's avatar
Stachu
Community Champion
8 years ago

the way I see it you should still be able to filter using slicers based on refCORRECTIONS (assuming there is join between it and refCLERICAL_TOTAL_MARK)
The way I see it, there should be 3 measures, we can test with first one to see if behaviour meets your expectations

[Old Mark] =
CALCULATE (
    SUM ( refCLERICAL_TOTAL_MARK[total_mark] ),
    FILTER (
        refCORRECTIONS,
        refCORRECTIONS[status] IN { "fully_approved", "initial_approval", "suggested" }
    )
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Stachu

Thanks for your response. I followed your example and got a number I was expecting but I feel I need to show more screenshots to demonstrate my trouble faced more clearly.

All data used is not real-world.

 

The SQL I'm looking to replicate is:

SELECT
          c.id AS [c.id]
          , c.status AS [c.status]
          , c.script_validation_id AS [c.script_validation_id]
          , c.correct_marks_opo
          , c.correct_marks_ssv
          , c.correct_mark
          , c.examiner_number_opo
          , c.examiner_number_ssv
          , c.examiner_number
          , SUM( ctm.total_mark ) AS [ctm.old mark]
          , CASE
                   WHEN c.status = 'fully_approved' THEN c.correct_marks_opo
                   WHEN c.status = 'initial_approval' THEN c.correct_marks_ssv
                   WHEN c.status = 'suggested' THEN c.correct_mark
                   ELSE '0'
           END AS [c.new mark/mark override]
          , CASE
                   WHEN c.status = 'fully_approved' THEN c.examiner_number_opo
                   WHEN c.status = 'initial_approval' THEN c.examiner_number_ssv
                   WHEN c.status = 'suggested' THEN c.examiner_number
                   ELSE '0'
           END AS [c.examiner override]
FROM CORRECTIONS AS c
INNER JOIN SCRIPT_VALIDATION AS sv
     ON c.script_validation_id = sv.id
INNER JOIN CLERICAL_TOTAL_MARK AS ctm
     ON sv.id = ctm.script_validation_id
WHERE c.status IN ( 'fully_approved', 'initial_approval', 'suggested' )
GROUP BY c.id, c.status, c.script_validation_id, c.correct_marks_opo, c.correct_marks_ssv
     , c.correct_mark, c.examiner_number_opo, c.examiner_number_ssv, c.examiner_number
     , c.correct_marks_opo, c.correct_marks_ssv, c.correct_mark

 

The above SQL returns the following dataset:

 

So I'm trying to replicate the above SQL in DAX and produce the same dataset.

Below is my attempt to replicate the SQL using DAX:

tblApprovedErrors =
               CALCULATETABLE (
               SUMMARIZE (
                                 refCORRECTIONS,
                                 refCORRECTIONS[id],
                                 refCORRECTIONS[status],
                                 refCORRECTIONS[script_validation_id],
                                 refCORRECTIONS[correct_marks_opo],
                                 refCORRECTIONS[correct_marks_ssv],
                                 refCORRECTIONS[correct_mark],
                                 refCORRECTIONS[examiner_number_opo],
                                 refCORRECTIONS[examiner_number_ssv],
                                 refCORRECTIONS[examiner_number],
                                 "Old Mark", SUMX (
                                          RELATEDTABLE ( refCLERICAL_TOTAL_MARK ),
                                          refCLERICAL_TOTAL_MARK[total_mark]
                                              ),
                                 "New Mark/Mark Override", SWITCH (
                                                                         refCORRECTIONS[status],
                                                                         "fully_approved", refCORRECTIONS[correct_marks_opo],
                                                                         "initial_approval", refCORRECTIONS[correct_marks_ssv],
                                                                         "suggested", refCORRECTIONS[correct_mark],
                                                                         BLANK ()
                                                                         ),
                                  "Examiner Override", SWITCH (
                                                              refCORRECTIONS[Status],
                                                              "fully_approved", refCORRECTIONS[examiner_number_opo],
                                                              "initial_approval", refCORRECTIONS[examiner_number_ssv],
                                                              "suggested", refCORRECTIONS[examiner_number],
                                                              BLANK ()
                                                              )
                                  ),
          FILTER (
                     refCORRECTIONS,
                     refCORRECTIONS[status] IN { "fully_approved", "initial_approval", "suggested" }
                     )
         )

 

THe above DAX returns the dataset:

 

 

As you can see the DAX version is bringing in two additional rows ( IDs 11 and 12 ). Why is this?  Can someone please inform me what I am doing wrong in my DAX as it's not the same as the SQL?

 

To note, the CORRECTIONS table joins to the SCRIPT_VALIDATION table and this table joins to the CLERICAL_TOTAL_MARKS table.

 

Thanks in advance.

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    I actually created the override measure as well, as it was interesting example - can you test?

    New Mark/Mark Override = 
VAR tabStatus =
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( refCORRECTIONS, refCORRECTIONS[status] ),
            "cm_opo", SUM ( refCORRECTIONS[correct_marks_opo] ),
            "cm_ssv", SUM ( refCORRECTIONS[correct_marks_ssv] ),
            "cm", SUM ( refCORRECTIONS[correct_mark] )
        ),
        "mark", SWITCH (
            [status],
            "fully_approved", [cm_opo],
            "initial_approval", [cm_ssv],
            "suggested", [cm],
            BLANK ()
        )
    )
RETURN
    SUMX ( tabStatus, [mark] )

    if it works then examiner override should be basically the same, with changes in the column references in SUMMARIZE

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Stachu

       

      Implementing your 'New Mark' formula, it returns the value of 41. Bringing in the c.id and c.status fields to show its granularity I get the following resultset:

       

      Implementing your Old Mark/Mark Override formula it returns:

       

      The interesting thing here is that the 'New Mark' formula matches the number of rows returned from the SQL query. The 'Old Mark' formula returns the number of rows from my attempted DAX query. Any ideas whats going on?

       

      I'm going through your 'Old Mark' formula now to understand what it's doing.

       

      Thanks for your help on this.

      • Stachu's avatar
        Stachu
        Community Champion
        8 years ago

        I don't really get the question
        from what I see the individual numbers are correct, are the totals off?
        The total for [Old Mark] will give the sum of refCLERICAL_TOTAL_MARK[total_mark] where status has one of the given values
        The total for [Mark Override] will give the sum of
        refCORRECTIONS[correct_marks_opo], refCORRECTIONS[correct_marks_ssv], refCORRECTIONS[correct_mark], for respectively
        fully approved, initial_approval and suggested

        is it not the intended behaviour?

        EDIT: I see some additional customers appearing in the DAX version of the formulas, I assume this is due to lack of filter coming from the following join in SQL

        INNER JOIN SCRIPT_VALIDATION AS sv
     ON c.script_validation_id = sv.id