Is this possible in DAX, or should I just import the SQL query as a dataset?
I am trying to get success and failure stats for jobs, and list the worst performers. I have the table which contains the data, in my PBI report. I just have no clue how it would be done in DAX. Assuming it is possible, is it worth the trouble?
SELECT JOB_NAME ,TOTAL_RUNS ,SUCCESS_COUNT ,FAILURE_COUNT ,CONVERT(DECIMAL(5,2),100.0 * (TOTAL_RUNS-FAILURE_COUNT) / TOTAL_RUNS) AS SUCCESS_PCT ,CONVERT(DECIMAL(5,2),100.0 * (TOTAL_RUNS-SUCCESS_COUNT) / TOTAL_RUNS) AS FAILURE_PCT FROM (SELECT JOB_NAME ,COUNT(JOB_NAME) TOTAL_RUNS ,SUM(CASE WHEN EXIT_CODE = '0' THEN 1 ELSE 0 END) AS SUCCESS_COUNT ,SUM(CASE WHEN EXIT_CODE <> '0' THEN 1 ELSE 0 END) AS FAILURE_COUNT FROM MyDb.dbo.Table1 GROUP BY JOB_NAME) X WHERE TOTAL_RUNS > 10 ORDER BY FAILURE_PCT desc