Converting SQL into DAX

[Anonymous]

Can anyone help me to convert below SQL into DAX?

"

SELECT *
FROM VW_PTP_NC_PHASE
WHERE VW_PTP_NC_PHASE[NC Number]
        IN (SELECT VW_PTP_NC_PHASE[NC Number]
            FROM VW_PTP_NC_PHASE

            WHERE VW_PTP_NC_PHASE[Workflow Phase Name]="Closed") "

Thank you.

[devanshi]

 Dax format =   FILTER( VW_PTP_NC_PHASE,
                                     CONTAINS( VALUES(VW_PTP_NC_PHASE[NC Number]),
                                                          CALCULATETABLE( VALUES(VW_PTP_NC_PHASE[NC Number]),                                                                 VW_PTP_NC_PHASE[Workflow Phase Name] = "Closed" ), VW_PTP_NC_PHASE[NC Number]                                        )
                             )

[OwenAuger · Accepted solution]

Hi there,

Here is how I would translate this to a DAX query:

EVALUATE
VAR NC_Number_Filter =
    CALCULATETABLE (
        VALUES ( W_PTP_NC_PHASE[NC Number] ),
        VW_PTP_NC_PHASE[Workflow Phase Name] = "Closed"
    )
RETURN
    CALCULATETABLE ( VW_PTP_NC_PHASE, NC_Number_Filter )

Where are you intending to use this?

There may be other considerations depending on the context, but essentially the same code should work elsewhere, such as in a measure.

Regards