Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Get Last 3 Non Blank values from a Column

Hello All, I have table as follows.   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   I.e ...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Greg_Deckler please have a look at the below reply.

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

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi Anonymous,

 

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

 

 

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

 

 

You can download attached .pbix file.

 

Best Regards,
Qiuyun Yu

  • prateekraina's avatar
    prateekraina
    Icon for Memorable Member rankMemorable Member
    9 years ago

    Hi v-qiuyu-msft,

     

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

    Regards,

    Prateek Raina

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Icon for Community Support rankCommunity Support
      9 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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