Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Here is my Data set: On August 28 and August 29 there are 2 counts.
RUN_DTExit_code
8/27/2020 | 0 |
8/28/2020 | 0 |
8/28/2020 | 1 |
8/28/2020 | 1 |
8/29/2020 | 0 |
8/29/2020 | 0 |
8/29/2020 | 1 |
8/30/2020 | 0 |
8/30/2020 | 1 |
I have used the following measures:
Here is my Data set: On August 28 and August 29 there are 2 counts.
RUN_DTExit_code
8/27/2020 | 0 |
8/28/2020 | 0 |
8/28/2020 | 1 |
8/28/2020 | 1 |
8/29/2020 | 0 |
8/29/2020 | 0 |
8/29/2020 | 1 |
8/30/2020 | 0 |
8/30/2020 | 1 |
I have used the following measures:
That worked, thank you!!
Based on the screenshot you have provided may be the following is what you are looking for:
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)
Please create the following measures:
Let me know if this is what you are looking for.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |