Convert SQL to DAX

[Anonymous]

SELECT
      newid() AS fact_tiger_id,
      comply.*, 
      CASE
        WHEN comply.tiger_point = 4 THEN 100
        ELSE 0
      END tiger_percentage
    FROM
    (
        SELECT
        process.*
        ,(SELECT
              CASE    
                  WHEN ROUND(CAST((SUM(is_comply)/COUNT(is_comply))*100 AS DOUBLE),0) = 100 then 1
                  ELSE 0
              END
          FROM fact_tiger_process 
          WHERE
            report_category_id = process.report_category_id
            AND date_id = process.date_id
            AND store_id = process.store_id
        ) AS comply_percategory
        ,(SELECT
              CASE    
                  WHEN ROUND(CAST(SUM(is_comply) AS DOUBLE),2) = 0 then 0
                  ELSE FLOOR(CAST((SUM(is_comply)/COUNT(is_comply))*100 AS DOUBLE))
              END
          FROM fact_tiger_process 
          WHERE
            report_category_id = process.report_category_id
            AND date_id = process.date_id
            AND store_id = process.store_id
        ) AS comply_percentage_percategory
        ,(SELECT SUM(comply_indicator)
          FROM temp_comply_indicator 
          WHERE
            date_id = process.date_id
            AND store_id = process.store_id
    ) AS tiger_point -- Summary total point from report category
    FROM
    (SELECT * FROM fact_tiger_process) process
    ORDER BY process.date_id, process.visit_id
    ) comply

I am trying to convert this SQL script into PBI DAX into Measure. Can anyone help? Anything would be appreciated. Thanks. 

[v-stephen-msft]

Hi @Anonymous ,

Maybe you don't have to convert.

Power Query gives you the flexibility to import data from wide variety of databases that it supports. It can run native database queries, which can save you the time it takes to build queries using the Power Query interface. 

For more details, please refer to

Import data from a database using native database query | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.