cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alhowarth
Helper I
Helper I

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

 

The SQL query produces data like this:

 
 

 

1 REPLY 1
Greg_Deckler
Super User IV
Super User IV

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors