Jul 06 2020 06:37 AM
In the conditional column, I want to create a rule that, if a column value is less than 5 days ago, then the value should be "pending"
Jul 06 2020 06:53 AM
Insert first in the wizard any date you wish. In formula bar it will be something like
= Table.AddColumn(#"Changed Type", "Custom", each if [A] > #date(2020, 7, 2) then "pending" else null)
and be sure formula works with your data. After in formula bar change #date(...) on Date.From(Date.AddDays(DateTime.LocalNow(),-5))
= Table.AddColumn(#"Changed Type", "Custom", each if [A] > Date.From(Date.AddDays(DateTime.LocalNow(),-5)) then "pending" else null)
Jul 06 2020 07:29 AM - edited Jul 06 2020 07:31 AM
It works but I explained it incorrectly and is not what I was expecting.
I already have a conditional column with rules, if else if else, I can choose a column with dates and add after a specific date, but I can only input a fixed date, for example june 2 which is 5 days ago, however that query will change often and I need it to apply this rule for the last 5 days no matter when do I update it.
Maybe the only way to make this work is with a custom column rather than a conditional column, but the conditional column is just easier to work with.
Jul 06 2020 07:45 AM
SolutionConditional column is only subset of custom columns with user interface to generate if ... then ... else
Anyway, you already have some statement with fixed date. Stay on step where this formula was generated (or use Advanced editor) and change concrete date which is in form #date(2020,7,2) or so, on
Date.From(Date.AddDays(DateTime.LocalNow(),-5))
Jul 08 2020 06:03 AM - edited Jul 08 2020 06:16 AM
I am trying to add this as a DAX step
Note that all rules with "Approved" have priority.
Jul 08 2020 09:36 AM
I'm not sure what do you mean under "DAX step"
Jul 08 2020 09:50 AM
Jul 08 2020 09:55 AM
Yes, but where? - you add calculated column or measure or what?
Jul 08 2020 10:11 AM - edited Jul 08 2020 10:14 AM
I decided to do the same but in Power Pivot since the data comes from an .atomsvc
I already added a column with the following code, but I want to add a line that, if the column [Date] has a day that is within the last 5 days, then it adds pending. Here is the code without that line.
New column is called [Result]
Column [Number] Is used to define if its approved or not, this one has priority no matter the others
Column [Date] Is used to define as "Pending" if its within the last 5 days
Else, it should be "Denied"
[Result]= SWITCH (
TRUE (),
'Test'[Number] = "X001", "Approved",
'Test'[Number] = "X002", "Approved",
'Test'[Number] = "X003", "Approved",
'Test'[Number] = "X004", "Approved",
'Test'[Number] = "X005", "Approved",
'Test'[Number] = "X006", "Approved",
'Test'[Number] = "X007", "Approved",
'Test'[Number] = "X008", "Approved",
'Test'[Number] = "X009", "Approved",
'Test'[Number] = "X010", "Approved",
"Denied"
)
My intention is that, unless is one of those number the result should be "Denied" with the exception of those with a number in the list I am adding, those should be "Approved" no matter if they have a date within the last 5 days.
Jul 08 2020 12:44 PM
Perhaps
=IF('Test'[Number] IN {"X001","X002","X003","X004","X005","X006","X007","X008","X009","X010"},
"Approved",
IF(TODAY()-'Test'[Date]<=5,"Pending","Denied"))
Jul 28 2020 09:08 AM
Sorry for the late reply but, how do you do that nice syntax? I use Visual studio code and is too plain simple.
Is there any "assistant" app to develop scripts in .m?
Jul 28 2020 12:48 PM
Above concrete code is just in formula bar in Power Pivot, like Excel formulas in it's formula bar. Additionally I use DAX Studio , but that's not the code generator.
Jul 06 2020 07:45 AM
SolutionConditional column is only subset of custom columns with user interface to generate if ... then ... else
Anyway, you already have some statement with fixed date. Stay on step where this formula was generated (or use Advanced editor) and change concrete date which is in form #date(2020,7,2) or so, on
Date.From(Date.AddDays(DateTime.LocalNow(),-5))