Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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.
Solved! Go to Solution.
I think i got the solution.
I did mentioned in Top N filtering of visual level filter of fields.
And it worked for me.
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],",")
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.
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.
@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:
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
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
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
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).
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |