Forum Discussion
Replicate this SQL in DAX
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.
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] )
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.
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