Forum Discussion

alhowarth's avatar
alhowarth
Icon for Helper I rankHelper I
6 years ago
Solved

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:

 

 

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

Need Help
  • lit2018pbi's avatar
    lit2018pbi
    6 years ago

    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:
     
     
    Let me know if this resolves your issue.

8 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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 ) ) )
)

     

    • alhowarth's avatar
      alhowarth
      Icon for Helper I rankHelper I
      6 years ago

      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.

       

  • lit2018pbi's avatar
    lit2018pbi
    Icon for Resolver II rankResolver II
    6 years ago

    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)

    • alhowarth's avatar
      alhowarth
      Icon for Helper I rankHelper I
      6 years ago

      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.

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

  • lit2018pbi's avatar
    lit2018pbi
    Icon for Resolver II rankResolver II
    6 years ago

    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:
     
     
    Let me know if this resolves your issue.
  • lit2018pbi's avatar
    lit2018pbi
    Icon for Resolver II rankResolver II
    6 years ago

    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)

     

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