Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi folks,
I'm hoping someone can help me. I'm trying to create a calculated column to show the time elapsed, in days, between when a piece of documentation was created [Created Date], and TODAY() - but only if the [Status] of the documentation is "Draft". If the [Status]<> "Draft", the calculation should return a blank. My column formula and some sample data is below. I have two challenges: My calculation below is returning results regardless of the [Status], and I'm also getting some (not all) negative numbers which I don't understand, looking at the [Created Date] field:
DRAFT_TIME = IF
(
CONTAINS('COMBINED_Articles','COMBINED_Articles'[Status],"Draft"),
DATEDIFF
(
[Created Date],TODAY(), DAY)
)
Any help would really be appreciated!
@Anonymous CONTAINS is not doing the job you thought it does. CONTAINS check if at least 1 row of the table is meeting the condition. CONTAINSTRING is your guy.
I would just do this:
DRAFT_TIME =
IF
(
CONTAINS('COMBINED_Articles','COMBINED_Articles'[Status],"Draft"),
([Created Date] - TODAY()) * 1. ,
BLANK()
)
Thanks so much for the quick feedback @Greg_Deckler - after your reply I realied that what I'm actually trying to create is a calculated column, not a measure - I do apologise. I've editied the original post accordingly...
I think that calculation should work in a column.
Maybe I'm misunderstanding the issue, and simply doing something wrong?
It looks for all the world that you are creating a measure. You want to create a column right?
Apologies again, I had gotten mixed up (that'll teach me for rushing) and was indeed trying to create a measure. I've sucessfully used your code to create a calculated column (thanks again), but 1) I'm getting negative numbers, and 2) it's not returning blank results for the non-'draft' articles:
Right, easy to fix. I was using your code for the IF true/false statement, I would do it this way:
DRAFT_TIME =
IF
(
COMBINED_Articles'[Status] <> "Draft",
(TODAY() - [Created Date]) * 1. ,
BLANK()
)
That works! Now the LAST problem is that some results are coming up in negatives - any idea why that might be happening?
So, that's fixable. Couple different ways of doing it but one method is:
DRAFT_TIME =
VAR __Table = { [Created Date], TODAY() }
VAR __DateMax = MAXX(__Table,[Value])
VAR __DateMin = MINX(__Table,[Value])
RETURN
IF
(
COMBINED_Articles'[Status] <> "Draft",
(__DateMax - __DateMin) * 1. ,
BLANK()
)
While I certainly appreciate your response and your time, I'm not sure that this has changed too much 🙂
Change your data type on your Created Date column to be Date Time column instead of text. If it was a Date or Date Time, it would be in italics in your screen shot.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |