DAX to SQL code help

Anonymous — Wed, 31 Oct 2018 14:57:07 GMT

HI Experts,

Could anyone please translate below DAX into SQL please

1. AIMS = IF(LEN(A.L)>=10 and IFERROR(VALUE(RIGHT(Asset[A_L],1)),FALSE()),"AIMS","ANIMS")

2. AIMS = IF(Asset[AGAL] IN DISTINCT('H S'[AG]) && A[AIMS]="AIMS","I M S","N i M S")

3. AGAL = A[A_L] & "_" & A[AG]

4. AIMLS = IF(Asset[AGAL IN DISTINCT('H S'[AG]) && Asset[AIMxS]="AIMXS","I M S","N in M S")

thanks

Re: DAX to SQL code help

technolog — Tue, 12 Sep 2023 10:20:21 GMT

Hi!

The DAX expressions you provided can be translated into SQL as follows:

For the DAX expression:
AIMS = IF(LEN(A.L)>=10 and IFERROR(VALUE(RIGHT(Asset[A_L],1)),FALSE()),"AIMS","ANIMS")
The SQL translation would be:

CASE
WHEN LEN(A.L) >= 10 AND ISNUMERIC(RIGHT(Asset.A_L, 1)) = 1 THEN 'AIMS'
ELSE 'ANIMS'
END AS AIMS
For the DAX expression:
AIMS = IF(Asset[AGAL] IN DISTINCT('H S'[AG]) && A[AIMS]="AIMS","I M S","N i M S")
The SQL translation would be:

CASE
WHEN Asset.AGAL IN (SELECT DISTINCT AG FROM H_S) AND A.AIMS = 'AIMS' THEN 'I M S'
ELSE 'N i M S'
END AS AIMS
For the DAX expression:
AGAL = A[A_L] & "_" & A[AG]
The SQL translation would be:

CONCAT(A.A_L, '_', A.AG) AS AGAL
For the DAX expression:
AIMLS = IF(Asset[AGAL IN DISTINCT('H S'[AG]) && Asset[AIMxS]="AIMXS","I M S","N in M S")
The SQL translation would be:

CASE
WHEN Asset.AGAL IN (SELECT DISTINCT AG FROM H_S) AND Asset.AIMxS = 'AIMXS' THEN 'I M S'
ELSE 'N in M S'
END AS AIMLS

topic Re: DAX to SQL code help in DAX Commands and Tips

DAX to SQL code help

Re: DAX to SQL code help