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.

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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