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
boclifton
Regular Visitor

Only show column value from top row of sorted data

Hi everyone,

 

I have what seems to be a simple question, but I'm new to Power BI and can't quite get a handle on it.  I have a table of data with a Date in a column; and a non-SUM'd value, a record of current failures in the data, so it can vary from row to row.  I just want to get the most recent value from the current failures column based on the Date column.

 

Example:

Date CurrentFailures

10/1/2016 33445

10/15/2016 500

10/30/2016 10666

11/1/2016 555

 

I'd like the measure (or column...?) to return 555.  Thanks in advance for any assistance!

1 ACCEPTED SOLUTION

@boclifton

 

Add Before The IF(HasOneValue([ClientTable[ClientColumn]); the measure)

 

So when is no selected a client the measure don't show a value.

 

The LastDate take all the dates with the last date. if you have three rows take all of them.




Lima - Peru

View solution in original post

10 REPLIES 10
Vvelarde
Community Champion
Community Champion

@boclifton

 

If you want to show the failures of last date in your table.

 

you can use the next measure:

 

CurrentFailure-in-LastDate =
CALCULATE ( VALUES ( Table1[CurrentFailures] ), LASTDATE ( Table1[Date] ) )



Lima - Peru
Stachu
Community Champion
Community Champion

if data is sorted then the 'Keep bottom rows' may work for you

in the query editor, go to 

Home > Keep Rows > Keep Bottom Rows



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

The data isn't sorted in the query.  Should I apply sorting in the query area? I thought that was supposed to be handled in the Report area by the visuals.  

 

(Sorry, like I said, I'm very new to Power BI...)

 

EDIT:  Also, I suppose I should have mentioned, there's other data in the table.  Not just the date and failures.  It's a table of transaction info for data transfers and CurrentFailures is just one of the columns.

Stachu
Community Champion
Community Champion

do you need the value in the front end on query editor?

if the former then @Vvelarde solution should work, if you need it in query editor then how do you plan to use it further? is it a parameter for some calculation?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

@Stachu Really, I just need the value for display as a "CurrentItemsFailed" column that will only have one value for each client.  I want to click the slicer I have in place that lists all my clients, then get a single result for "CurrentItemsFailed". 

 

@Vvelarde I think this is almost it.  Power BI complained that it had duplicate entries for LastDate so it couldn't be used.  I have multiple clients dumping data into this table, so I still need it to respond to slicers for clientID, as well.  I've included a screenshot of the top couple of rows in the table.

 

Thanks for all your help, guys!  I feel like it's almost there.

 

Screen Shot 2016-11-11 at 2.12.10 PM.png

@boclifton

 

Let me ask you some questions;

 

When you have two or more row with same last date what result do you want to show in Current Failures? SUM, Average..?

 

 

 

 

 

 

 




Lima - Peru

@Vvelarde That should only happen when I have no clients selected, so a SUM should be fine.

 

Also, most entries are going to have 4 entries per day (or more).  Will LASTDATE go to the latest date/time, or just the date portion?  I.E. if I have 3 entries on 10/1, will it complain, or will it display the latest time?

@boclifton

 

Add Before The IF(HasOneValue([ClientTable[ClientColumn]); the measure)

 

So when is no selected a client the measure don't show a value.

 

The LastDate take all the dates with the last date. if you have three rows take all of them.




Lima - Peru

Thanks to your help (both of you!), I was able to come up with this.

 

Incomplete = IF(HASONEVALUE(Clients[ClientName]), CALCULATE(VALUES(Transactions[CurrentItemsFailed]), LASTNONBLANK(Transactions[EndTime], Transactions[EndTime])))

 

This seems to be doing the trick, and I've verified the data against my Azure DB.  Thanks guys!

Stachu
Community Champion
Community Champion

yes, in the Query area

alternatively, if the field format is Date then you can apply the Date filter 'Is latest' in PowerQuery - this may return multiple values though if one date can have multiple rows



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.