Replicate this SQL in DAX

[Anonymous]

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 AS [sv.id]
     , ctm.script_validation_id AS [ctm.script_validation_id]
     , 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]
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, sv.id, ctm.script_validation_id,
     c.correct_marks_opo, c.correct_marks_ssv, c.correct_mark

----------------------------------------------------------------------------------------

This is my DAX attempt so far:

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" }
                                                                                            )
                                                           )

Thanks in advance.

[Stachu]

is the new table absolutely necessary? I think it may be easier to write new measure that would calculate whatever KPI you would need to get from the calculated table, rather than creating the table itself. Is that an option?

[Anonymous]

My thinking is to create a table that will hold a number of rows, based on the grouping and joining shown in the SQL, and that table can be filtered by some slicers on the visual.

By creating a measure it would be creating a single KPI where in fact I require a table that can be filtered by column values. Now, I may be wrong but I am thinking I do a table to achieve this. If I am wrong then please make me aware of a better way?
Thanks.

[Stachu]

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]

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]

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]

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]

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]

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.

[Stachu]

so we need to extend filters - the joins are 1:1 and filtering in both directions?

this should work

New Mark/Mark Override =
VAR tabStatus =
    ADDCOLUMNS (
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE ( refCORRECTIONS, refCORRECTIONS[status] ),
                INTERSECT (
                    ALL ( SCRIPT_VALIDATION[id] ),
                    VALUES ( refCLERICAL_TOTAL_MARK[Id] )
                )
            ),
            "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] )

[Anonymous]

@Stachu

I've implemented what you've provided in your most recent post. I can see what you are doing, and if I am correct in that, I had to tweak the:

VALUES ( refCLERICAL_TOTAL_MARK[Id] )

 to reference 'refCLERICAL_TOTAL_MARK[script_validation_id].

The problem is, when I use the measure but bring in other fields/columns from the tables in the data model it makes it so the IDs 11 and 12 are shown in the visual. This is the reason I was heading down the route of a calculated table. I wanted to restrict the rows by those that only appear in the SCRIPT_VALIDATION,  CORRECTIONS,  and CLERICAL_TOTAL_MARK tables. Put these restricted rows in a table and then the user can swap fields/columns from the calcated table.

Right now, I can't see the wood for the trees.

I'm digesting your queries to understand different techniques. Although it's not doing as I wanted, I'll be suprised if what I want isn't acheivable but I don't know how to acheive it at the moment, I will give you a thumbs up (kudos) because you have been extremely helpful. Thanks.

[Stachu]

hmm, I see few ways of approaching this:

1) include in every measure you show:

                INTERSECT (
                    ALL ( SCRIPT_VALIDATION[id] ),
                    VALUES ( refCLERICAL_TOTAL_MARK[script_validation_id] )
                )

which is annoying

2) removing the troublesome ids in PowerQuery - but most likely it's not an option? do you need them in other views/visuals?
3) adding the piece of code I posted earlier to yor calculated table