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
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
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Stachu

        Yes, I may have been confusing in my questioning.

         

        Okay, I'll put it another way. My SQL query has less entries as there is an INNER JOIN condition between the SCRIPT_VALIDATION table and the CLERICAL_TOTAL_MARK table ( INNER JOIN CLERICAL_TOTAL_MARK AS ctm ON sv.id = ctm.script_validation_id ).

         

        Now the  CLERICAL_TOTAL_MARK table doesn't have IDs 11 and 12, and that's why in my original SQL query (see previous post) these IDs weren't appearing. This is what I wish to replicate in DAX.

         

        Your 'Old Mark' DAX query works with the contents of the CLERICAL_TOTAL_MARK table, with filtering from the CORRECTIONS table. Bringing this measure into a table with the ID field omits IDs 11 and 12. This is what I want - good.

         

        Your 'New Mark' DAX query works with the contents of the CORRECTIONS table. The CORRECTIONS table does have IDs 11 and 12. To add, the SCRIPT_VALIDATION table has the IDs 11 and 12 too.

         

        The relationship between these tables in Power Query is:
        CLERICAL_TOTAL_MARK.script_validation_id  >>  SCRIPT_VALIDATION.id  >>  CORRECTIONS.script_validation_id

         

        Somehow I need the DAX, wether it be my DAX table (see first post) or your DAX measures, to only return the rows where the id appears in all three tables.

         

        Does this make better sense?

        Thanks again.