Forum Discussion
Power ParticipantConvert DAX to M Code
Hi ArchStanton,
That doesn't look right. Let's try that again.
- Create a new blank query,
- rename it to: fxCaseLength,
- open the advanced editor select all you see in there
- paste the function code shared earlier - in its place
These steps create a function query, named: fxCaseLength
Now select the Cases query you want to apply this logic to.
- Go to the Add column tab on the ribbon
- Press the Invoke Custom Function option
The depicted dialog box shows up
- give the column a name: Case Length
- select the function query: fxCaseLength
- map each argument to a column
- switch to column input
- select the column name from the drop down
Here's what that looks like, in your Cases query, when only the first two arguments were mapped.
I hope this is helpful
Hi ArchStanton,
Give update a go(statecode, legacyCreated, createdOn, resolutionDate) => let start = try Date.From(legacyCreated ?? createdOn) otherwise null, isActive = Text.Proper(statecode) = "Active", end = if isActive then Date.From(DateTimeZone.FixedUtcNow()) else (try Date.From(resolutionDate) otherwise null), days = try Duration.Days(end - start) otherwise null, n = if not isActive and Value.Equals(days, 0) then 1 else days in n
Resident RockstarHi ArchStanton
Alternatively, you can create a custom function like below. That makes it easy to implement your logic accross different projects, name it: fxCaseLength
(statecode, legacyCreated, createdOn, resolutionDate) =>
let
start = try Date.From(legacyCreated ?? createdOn) otherwise null,
end = if Text.Proper(statecode) = "Active"
then Date.From(DateTimeZone.FixedUtcNow())
else (try Date.From(resolutionDate) otherwise null),
days = try Duration.Days(end - start) otherwise null
in
days
Then you can use the Invoke Custom Function option from the Add Column tab. Select the fxCaseLength function query and set Column inputs for all its arguments.
Power ParticipantHi, can you add the folliwing to the code please?
If the Createdon or Legacy Created On date is the same as the Resolution date then it should be 1 not zero.
Thanks
- m_dekorte
Hi ArchStanton,
Give update a go(statecode, legacyCreated, createdOn, resolutionDate) => let start = try Date.From(legacyCreated ?? createdOn) otherwise null, isActive = Text.Proper(statecode) = "Active", end = if isActive then Date.From(DateTimeZone.FixedUtcNow()) else (try Date.From(resolutionDate) otherwise null), days = try Duration.Days(end - start) otherwise null, n = if not isActive and Value.Equals(days, 0) then 1 else days in n- ArchStanton
Brilliant!
Thanks so much