Reply
Highlighted
Member
Posts: 277
Registered: ‎02-14-2017
Accepted Solution

Get Last 3 Non Blank values from a Column

Hello All,

I have table as follows.

1.PNG

 

Here i need to get the values of 5/31/2017, 5/30/2017 and 5/29/2017 values from comments columns

if my table contains comments column as

 

2.PNG

I.e the last value is not available then it should get the values of last three dates values i.e 31, 30, 29.

 

Any suggestions.

Mohan V.


Accepted Solutions
Member
Posts: 277
Registered: ‎02-14-2017

Re: Get Last 3 Non Blank values from a Column

I think i got the solution.

I did mentioned in Top N filtering of visual level filter of fields.

enter image description here

And it worked for me.

View solution in original post


All Replies
New Contributor
Posts: 504
Registered: ‎02-16-2017

Re: Get Last 3 Non Blank values from a Column

Hi @MohanV,

 

Can you please elaborate on how are you going to use those values?
Simply getting the value doesn't make sense.

Regards,

Prateek Raina

Super User
Posts: 9,834
Registered: ‎07-11-2015

Re: Get Last 3 Non Blank values from a Column

Will have to do some work on this, but how about something like this?

 

Last3Blanks = var LastBlank = CALCULATE(MAX('Calendar'[Date]),FILTER('Calendar','Calendar'[Comments]=""))
RETURN CONCATENATEX(CALCULATETABLE(VALUES('Calendar'[Date]),FILTER('Calendar','Calendar'[Date]<=LastBlank && 'Calendar'[Date] >= LastBlank-2)),'Calendar'[Date],",")

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

Proud to be a Datanaut!


Member
Posts: 277
Registered: ‎02-14-2017

Re: Get Last 3 Non Blank values from a Column

[ Edited ]

Thanks for the reply @prateekraina & @Greg_Deckler

 

Actually i have a table where i get the status of the emplyees task day by day.

As in below image i get the values(Confidential, so just enterd dumy data).

3.PNG

 

 

Then i have unpivoted the dates columns and made a copy of that column and to that column splitted with space so that i can get the date value.

4.PNG

 

there will be number of tasks and i get this kind of data everyday.

Here i need to show the last 3 commneted values in a table visualization for each task.

Like as i mentioned above if the 5/31/2017 value is not available then it should get the values of 5/29/2017 plan, 5/29/2017 actual, 5/30/2017 plan values i.e nothing but the last 3 non blank values.

 

If you need any furthur info please feel free to ask.

 

Mohan.V

 

 

 

If i need to get the la

Member
Posts: 277
Registered: ‎02-14-2017

Re: Get Last 3 Non Blank values from a Column

Thanks for the reply @Greg_Deckler

I did tried what you have suggested.

i didnt get any errors but i didnt get any output also.

5.PNG

 

Super User
Posts: 9,834
Registered: ‎07-11-2015

Re: Get Last 3 Non Blank values from a Column

Hmm, what data set are you using with that? Looks OK formula-wise. I did not try the use case where there are no blanks.

 

 


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

Proud to be a Datanaut!


Member
Posts: 277
Registered: ‎02-14-2017

Re: Get Last 3 Non Blank values from a Column

@Greg_Deckler please have a look at the below reply.

I have given info about the data that i am working.

Member
Posts: 277
Registered: ‎02-14-2017

Re: Get Last 3 Non Blank values from a Column

I think i got the solution.

I did mentioned in Top N filtering of visual level filter of fields.

enter image description here

And it worked for me.

Moderator
Posts: 9,122
Registered: ‎03-06-2016

Re: Get Last 3 Non Blank values from a Column

Hi @MohanV,

 

You can also create a index column in Power Query with code below:

 

e1.PNG

 

Then create a measure:

 

Last3Blanks = var l= CALCULATE(MAX('Calendar'[Index]),FILTER(ALL('Calendar'),'Calendar'[Task]=MAX('Calendar'[Task]) &&'Calendar'[Comment]=BLANK() ))
return
CONCATENATEX(CALCULATETABLE('Calendar',FILTER('Calendar','Calendar'[Index]<l && 'Calendar'[Index]>=l-3)),'Calendar'[Date],",")

 

e2.PNG

 

You can download attached .pbix file.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
New Contributor
Posts: 504
Registered: ‎02-16-2017

Re: Get Last 3 Non Blank values from a Column

Hi @v-qiuyu-msft,

 

Out of curiosity, how did you attach the zip file in a reply? Smiley Very Happy

Regards,

Prateek Raina