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

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

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's avatar
    Stachu
    Community Champion
    8 years ago

    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