Forum Discussion
DAX Help: Extract value based on last execution
Hi,
I have a below scenario:
| Timestamp | CaseID | CaseTitle | Status |
| 8-16-2017 5:00 | 1 | TestCase A | Pass |
| 8-16-2017 5:00 | 2 | TestCase B | Pass |
| 8-16-2017 5:00 | 3 | TestCase C | Pass |
| 8-16-2017 5:00 | 4 | TestCase D | FAIL |
| 8-16-2017 5:00 | 5 | TestCase E | FAIL |
| 8-16-2017 10:00 | 1 | TestCase A | Pass |
| 8-16-2017 10:00 | 2 | TestCase B | FAIL |
| 8-16-2017 10:00 | 3 | TestCase C | FAIL |
| 8-16-2017 10:00 | 4 | TestCase D | FAIL |
| 8-16-2017 10:00 | 5 | TestCase E | FAIL |
One set of cases executed twice daily. I need to show the count of Pass/Fail based on the last execution.
How can I achieve this?
Currently i group them via date, it shows me 4 pass and 6 fail,.
The desired output should be 1 Pass/ 4 Fail
Hey,
maybe this would be a solution, I'm counting the caseid (distinct count) to get the numbers in this table
I did not use a measure I just changed the Aggregate function in the visuals pane and renamed the column in the visual
Maybe now I'm getting closer
14 Replies
How about some measures like this:
Pass = CALCULATE(COUNTA('Cases'[Status]),FILTER('Cases','Cases'[Timestamp]=MAX('Cases'[Timestamp])),FILTER('Cases',[Status]="Pass") )
Fail = CALCULATE(COUNTA('Cases'[Status]),FILTER('Cases','Cases'[Timestamp]=MAX('Cases'[Timestamp])),FILTER('Cases',[Status]="Fail") )you could also do this cshashank
create the max date
(its a measure)
lastdate = max(Table1[Timestamp])
and then create another measure
Measure 2 = CALCULATE(COUNTROWS('Table1'), FILTER('Table1', Table1[Timestamp] = max(Table1[Timestamp])))
place them both on a visual
- Can you please help me with how to group the data based upon date and later by time?
Please forgive my slow thinking, but I have no idea what is do mean by grouping, please provide a visual like so
And also provide input what approach of the suggestions you are using
Hey,
I created a calculated column using this DAX
Latest Status = var currentCaseID = 'Table1'[CaseID] var maxTimeStamp = CALCULATE( MAX('Table1'[Timestamp]) ,ALLEXCEPT('Table1',Table1[CaseID],Table1[CaseTitle]) ) return LOOKUPVALUE(Table1[Status], 'Table1'[Timestamp], maxtimestamp, Table1[CaseID], currentCaseID)The values for this column will look like this
A possible visualization could be
Hope this helps somehow
How can I achieve the desired result?
Its still going to show me 2 pass 8 Fail
How can I achieve the desired result?
Its still going to show me 2 pass 8 Fail
Hey,
can you please share which approach you are using and provide an image of your visualization.
Cheers
