DAX to SQL code help

[Anonymous]

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 

[technolog · Accepted solution]

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