cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cshashank Frequent Visitor
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

Accepted Solutions
Super User
Super User

Re: DAX Help: Extract value based on last execution

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 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
14 REPLIES 14
Super User
Super User

Re: DAX Help: Extract value based on last execution

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

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

emudria Member
Member

Re: DAX Help: Extract value based on last execution

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 .

vanessafvg Super Contributor
Super Contributor

Re: DAX Help: Extract value based on last execution

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super User
Super User

Re: DAX Help: Extract value based on last execution

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

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
cshashank Frequent Visitor
Frequent Visitor

Re: DAX Help: Extract value based on last execution

How can I achieve the desired result?

 

Its still going to show me 2 pass 8 Fail

cshashank Frequent Visitor
Frequent Visitor

Re: DAX Help: Extract value based on last execution

How can I achieve the desired result?

 

Its still going to show me 2 pass 8 Fail

Super User
Super User

Re: DAX Help: Extract value based on last execution

Hey,

 

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

 

Cheers

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
cshashank Frequent Visitor
Frequent Visitor

Re: DAX Help: Extract value based on last execution

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

Re: DAX Help: Extract value based on last execution

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.

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 271 members 2,665 guests
Please welcome our newest community members: