Convert DAX to M Code

[ArchStanton]

My M knowledge is limited so I was wondering if someone could show me how I could write this DAX code as M?

I have several such columns in my data model that are iterating over 50,000 rows - I therefore would like to build these in Power Query beforehand as it will be much less expensive to do so.

Case Length = 
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        DAY
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        DAY
    )
)

Thanks

[m_dekorte · Accepted solution]

Hi @ArchStanton,

That doesn't look right. Let's try that again.

1. Create a new blank query,
2. rename it to: fxCaseLength,
3. open the advanced editor select all you see in there
4. 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.

1. Go to the Add column tab on the ribbon
2. Press the Invoke Custom Function option

The depicted dialog box shows up

1. give the column a name: Case Length
2. select the function query: fxCaseLength
3. map each argument to a column
   1. switch to column input
   2. 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

[v-saisrao-msft]

Hi @ArchStanton,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? 

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

[v-saisrao-msft]

Hi @ArchStanton,

Have you had a chance to review the solution we shared by @Poojara_D12 @wdx223_Daniel @m_dekorte @Cookistador ? If the issue persists, feel free to reply so we can help further.

Thank you.

[Poojara_D12]

Hi @ArchStanton 

let
    Source = #"PreviousStepName",
    CurrentDate = DateTime.LocalNow(),
    AddCaseLength = Table.AddColumn(
        Source,
        "Case Length",
        each 
            let
                StartDate = if [legacycasecreationdate] = null then [Created On] else [legacycasecreationdate],
                EndDate = if [statecode] = "Active" then CurrentDate else [Resolution Date],
                DurationDays = Duration.Days(EndDate - StartDate)
            in
                DurationDays
    )
in
    AddCaseLength

[ArchStanton]

Hi, when I tried your solution I got this:

When I open the table I have every column in the database listed

[wdx223_Daniel]

NewStep=Table.AddColumn(YourSource,"Case Length",each Duration.Days((if [statecode]="Active" then DateTime.LocalNow() else [Resolution Date])-([legacycasecreationdate]??[Created On])))

[m_dekorte]

Hi @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.

[ArchStanton]

Hi, 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 · Accepted solution]

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

[ArchStanton]

Hi, I'm not sure what this solution is going to give me, will I be able to have the Case Length as a column in may main Fact table where I can use DAX on it if necessary?

I have implemented your suggestion but I don't really understand what invoking a custom function is and does.
This is what I can see after following your steps:

[m_dekorte · Accepted solution]

Hi @ArchStanton,

That doesn't look right. Let's try that again.

1. Create a new blank query,
2. rename it to: fxCaseLength,
3. open the advanced editor select all you see in there
4. 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.

1. Go to the Add column tab on the ribbon
2. Press the Invoke Custom Function option

The depicted dialog box shows up

1. give the column a name: Case Length
2. select the function query: fxCaseLength
3. map each argument to a column
   1. switch to column input
   2. 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

[ArchStanton]

This worked, thank you!!

[Cookistador]

Hello @ArchStanton 

The following M measure code should return what you need

Like with the VAR in dax code, we are using variables to get something easier to read

let
    StartDate =
        if [legacycasecreationdate] <> null then
            [legacycasecreationdate]
        else
            [Created On] ,

    EndDate =
        if [statecode] = "Active" then
            Date.From(DateTime.LocalNow())
        else
            [Resolution Date],

    DurationInDays =
        if StartDate <> null and EndDate <> null then
            Duration.Days(EndDate - StartDate)
        else
            null
in
    DurationInDays

You just have to paste this code in the pop-up of the creation of a custom column and you will have the desired column

[ArchStanton]

Hi,
I got this error when I tried: