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
cshashank
Frequent Visitor

DAX Help: Extract value based on last execution

Hi,

 

I have a below scenario:

TimestampCaseIDCaseTitleStatus
8-16-2017 5:001TestCase APass
8-16-2017 5:002TestCase BPass
8-16-2017 5:003TestCase CPass
8-16-2017 5:004TestCase DFAIL
8-16-2017 5:005TestCase EFAIL
8-16-2017 10:001TestCase APass
8-16-2017 10:002TestCase BFAIL
8-16-2017 10:003TestCase CFAIL
8-16-2017 10:004TestCase DFAIL
8-16-2017 10:005TestCase EFAIL

 

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

1 ACCEPTED SOLUTION

Hey,

 

maybe this would be a solution, I'm counting the caseid (distinct count) to get the numbers in this table

Value of latest Execution 3.png

I did not use a measure I just changed the Aggregate function in the visuals pane and renamed the column in the visual

Value of latest Execution 4.png

 

Maybe now I'm getting closer 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

14 REPLIES 14
TomMartens
Super User
Super User

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

Value of latest Execution.png

 

A possible visualization could be

Visualization of latest Value.png 

 

Hope this helps somehow

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

 

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

 

Hey,

 

maybe this would be a solution, I'm counting the caseid (distinct count) to get the numbers in this table

Value of latest Execution 3.png

I did not use a measure I just changed the Aggregate function in the visuals pane and renamed the column in the visual

Value of latest Execution 4.png

 

Maybe now I'm getting closer 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

How can I achieve the desired result?

 

Its still going to show me 2 pass 8 Fail

Anonymous
Not applicable

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 .

  • 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

Value of latest Execution 2.png

And also provide input what approach of the suggestions you are using



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.