Forum Discussion

Anonymous98's avatar
Anonymous98
New Member
2 years ago

How to convert SQL to DAX

Hi Everyone,
 
Can someone help me convert this SQL query to DAX for creating new measure. I tried several ways but could not figure it out if someone could help out I would really appreciate it. Thanks in Advance.
 
WITH SANAL AS (
  SELECT DISTINCT "COLUMN A","COLUMN B", "COLUMN C",
    CASE
    WHEN TRIM("MONTH") = '-1' THEN 0
    WHEN TRIM("MONTH") = 'OCAK' THEN 1
    WHEN TRIM("MONTH") = 'ŞUBAT' THEN 2
    WHEN TRIM("MONTH") = 'MART' THEN 3
    WHEN TRIM("MONTH") = 'NISAN' THEN 4
    WHEN TRIM("MONTH") = 'MAYIS' THEN 5
    WHEN TRIM("MONTH") = 'HAZIRAN' THEN 6
    WHEN TRIM("MONTH") = 'TEMMUZ' THEN 7
    WHEN TRIM("MONTH") = 'AĞUSTOS' THEN 8
    WHEN TRIM("MONTH") = 'EYLÜL' THEN 9
    WHEN TRIM("MONTH") = 'EKIM' THEN 10
    WHEN TRIM("MONTH") = 'KASIM' THEN 11
    WHEN TRIM("MONTH") = 'ARALIK' THEN 12
    END as "MONTH",
    CASE
    WHEN "COLUMN D" = -1 THEN 0
    WHEN "COLUMN D" = 2022 THEN 1
    WHEN "COLUMN D" = 2023 THEN 3
    ELSE "COLUMN D" END as "COLUMN D" FROM power_bi_test
),KUMULE_FILTRELEME AS(
SELECT DISTINCT Row_number() OVER(PARTITION BY "COLUMN A" ORDER BY "COLUMN A","COLUMN D" DESC,"MONTH" DESC),
    "COLUMN A", "COLUMN D","MONTH", "COLUMN B","COLUMN C" FROM SANAL AA
   
    ),COLUMN C AS(
SELECT DISTINCT Row_number() OVER(PARTITION BY "COLUMN C","COLUMN A" ORDER BY "COLUMN A" DESC,"COLUMN D" DESC,"MONTH" DESC),"COLUMN A",
   "COLUMN B","COLUMN C","COLUMN D","MONTH" FROM SANAL AA
    WHERE "COLUMN C" IN(
    SELECT "COLUMN C" FROM SANAL WHERE "COLUMN B" = 'Boş'
    )
    AND "COLUMN B" != 'Boş'
),COLUMN C_FILTRELEME AS(
SELECT * FROM COLUMN C BB WHERE row_number=1
),
SANAL1 AS (
    SELECT
        "COLUMN A",
         CASE
            WHEN "COLUMN B" = 'Boş' THEN (
                SELECT MAX("COLUMN B")
                FROM COLUMN C_FILTRELEME BB
                WHERE BB."COLUMN C" = AA."COLUMN C"
                AND BB."COLUMN B" != 'Boş'
            )
            ELSE "COLUMN B"
        END AS "COLUMN B"
    FROM KUMULE_FILTRELEME AA
    WHERE row_number = 1
    GROUP BY "COLUMN A", "COLUMN B", "COLUMN C"
)

SELECT "COLUMN A", "COLUMN B" FROM SANAL1


@Dax help
@Daxhelp
SQL to DAX

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous98 ,

    In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.