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
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

11 REPLIES 11
Anonymous
Not applicable

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.

Greg_Deckler
Super User
Super User

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

@ 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...
Anonymous
Not applicable

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

 

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.

 

 


@ 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...
Anonymous
Not applicable

@Greg_Deckler please have a look at the below reply.

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

Hi @Anonymous,

 

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.

hi @v-qiuyu-msft how to create index column for particular column in direct query ???

Hi @v-qiuyu-msft,

 

Out of curiosity, how did you attach the zip file in a reply? 😄

Regards,

Prateek Raina

Hi @prateekraina,

 

I'm a Moderator role in this forum, so I can attach files.

 

You can upload any files to your OneDrive and share the link here.

 

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.
prateekraina
Memorable Member
Memorable Member

Hi @Anonymous,

 

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

Regards,

Prateek Raina

Anonymous
Not applicable

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

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.