Forum Discussion
Replicate this SQL in DAX
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
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.
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 SQLINNER JOIN SCRIPT_VALIDATION AS sv ON c.script_validation_id = sv.idHi 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_idSomehow 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.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] )