Forum Discussion
Helper IIs 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
The SQL query produces data like this:
1 Reply
- Greg_Deckler
Community Champion
alhowarth - I don't see the sample data. If you don't need the raw source data you could just run the SQL in a query. Otherwise, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
