topic Re: Converting SQL into DAX in DAX Commands and Tips

Converting SQL into DAX

Anonymous — Wed, 21 Jun 2023 22:41:13 GMT

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.

Re: Converting SQL into DAX

OwenAuger — Thu, 22 Jun 2023 04:52:54 GMT

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

Re: Converting SQL into DAX

devanshi — Thu, 22 Jun 2023 05:09:29 GMT

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] )
)