DAX Help: Extract value based on last execution

[cshashank]

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

[TomMartens]

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

[cshashank]

How can I achieve the desired result?

Its still going to show me 2 pass 8 Fail

[TomMartens]

Hey,

can you please share which approach you are using and provide an image of your visualization.

Cheers

[cshashank]

I used your approach only, and get the last status successfully,

Now i need to show the Numbers, or passing percentage/pie chart, and that is causing a problem.

[TomMartens · Accepted solution]

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 

[cshashank]

Thank you so much, 

I took the hint from smoupre solution and create a measure like: 

FAIL= CALCULATE(DISTINCTCOUNT(testcasetbl[TCtitle]),FILTER('SmokeCase',[Latest Status]="FAIL"))

But your solution is definitely much simpler. Thanks a alot.

[TomMartens]

Glad we were able to help once again.

Maybe you also consider to create another measure lilke this

No of Cases = 
CALCULATE(
	DISTINCTCOUNT('Table1'[CaseID])
) 

Personally I find such a measure really beneficial in combination with columns like "latest status"

Cheers

[cshashank]

How can I achieve the desired result?

Its still going to show me 2 pass 8 Fail

[Anonymous]

This should be giving you the desired results since your date col also got time. If not, may be put the time of execution in a seperate col and then group the data based upon date and later by time .

[cshashank]

• Can you please help me with how to group the data based upon date and later by time?

[TomMartens]

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

[cshashank]

I just wanted to show Pass/Fail in Pie Chart rather than column chart.

[vanessafvg]

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

[Greg_Deckler]

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