The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |