Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alhowarth
Helper I
Helper I

Line chart for success vs failures, and DAX help

I am working with scheduled-jobs data.  I would like to create a chart to show the number of success and failures per day.

I'm thinking that this would be a line chart, with one line for success and another line for failure.

I tried this in Power BI using Count which only gave me the total count, and Count(Distinct) which told me some days there were 3 different exit codes, and some days there were 1.  My data should look like this:

 

SQL.png

 

My SQL would be this, but I can't figure out what this would be in DAX:

SELECT
RUN_DT
,COUNT(EXIT_CODE) Daily_Count
,EXIT_CODE
FROM (SELECT
RUN_DT
,CASE WHEN EXIT_CODE = 0
THEN 0
ELSE 1
END AS EXIT_CODE
FROM .dbo.RUNS
)X
GROUP BY EXIT_CODE, RUN_DT

1 ACCEPTED SOLUTION
lit2018pbi
Resolver II
Resolver II

@alhowarth 

 

Here is my Data set: On August 28 and August 29 there are 2 counts.

 

RUN_DTExit_code

8/27/20200
8/28/20200
8/28/20201
8/28/20201
8/29/20200
8/29/20200
8/29/20201
8/30/20200
8/30/20201

 

I have used the following measures:

Success = CALCULATE(COUNT('Table'[Exit_code]),'Table'[Exit_code]=0)
Failure = CALCULATE(COUNT('Table'[Exit_code]),'Table'[Exit_code]=1)
 
Here's the final visual:
 
result.PNG
 
Let me know if this resolves your issue.

View solution in original post

8 REPLIES 8
lit2018pbi
Resolver II
Resolver II

@alhowarth 

 

Please mark it as the solution if it resolved your issue. Thanks!

lit2018pbi
Resolver II
Resolver II

@alhowarth 

 

Here is my Data set: On August 28 and August 29 there are 2 counts.

 

RUN_DTExit_code

8/27/20200
8/28/20200
8/28/20201
8/28/20201
8/29/20200
8/29/20200
8/29/20201
8/30/20200
8/30/20201

 

I have used the following measures:

Success = CALCULATE(COUNT('Table'[Exit_code]),'Table'[Exit_code]=0)
Failure = CALCULATE(COUNT('Table'[Exit_code]),'Table'[Exit_code]=1)
 
Here's the final visual:
 
result.PNG
 
Let me know if this resolves your issue.

That worked, thank you!!

lit2018pbi
Resolver II
Resolver II

@alhowarth 

 

Based on the screenshot you have provided may be the following is what you are looking for:

Failure = CALCULATE(SUM('Table'[Daily_count]),'Table'[Exit_code]=0)
Success = CALCULATE(SUM('Table'[Daily_count]),'Table'[Exit_code]=1)

tet1.PNG

Your screenshot is exactly what I want to see.

 

I had to switch the values at the end as 0 is success, and 1 is failure.  Yet my success line is flat.

alhowarth_0-1600803886897.png

Daily_Failure = CALCULATE(SUM('table'[EXIT_CODE])|'table'[Exit_code]=1)
Daily_Success = CALCULATE(SUM('table'[EXIT_CODE])|'table'[EXIT_CODE]=0)

lit2018pbi
Resolver II
Resolver II

@alhowarth 

 

Please create the following measures:

Failure = CALCULATE(COUNT('Table'[Exit_code]),'Table'[Exit_code]=0)
Success = CALCULATE(COUNT('Table'[Exit_code]),'Table'[Exit_code]=1)
tet.PNG

 

Let me know if this is what you are looking for.
 
Thanks
 
 
 
CNENFRNL
Community Champion
Community Champion

Hi, @alhowarth , based on your description, a DAX equivalent of your sql would be like this,

=
SUMMARIZECOLUMNS (
    'Table'[RUN_DT],
    'Table'[EXIT_CODE],
    "Daily_Count", CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[EXIT_CODE] <> 0 ) ) )
)

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

I received the dreaded error: The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scalar Value.

error. 
After some Googling, I found a suggestion to use:  COUNTROWS( ).  
Then, I was able to save the Measure without error, but it wouldn't work on my chart. 

MdxScript(Model) (4, 54) Calculation error in measure 'table'[Measure]: SummarizeColumns() and AddMissingItems() may not be used in this context.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.