Power Participant

9 months ago

Solved

Convert DAX to M Code

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

Need Help

Show and Tell

m_dekorte
9 months ago
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

m_dekorte

9 months ago

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

14 Replies

m_dekorte
Resident Rockstar
9 months ago
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
  Power Participant
  9 months ago
  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
    Resident Rockstar
    9 months ago
    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
- ArchStanton
  Power Participant
  9 months ago
  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
    Resident Rockstar
    9 months ago
    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
v-saisrao-msft
Community Support
9 months ago
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.

Cookistador

Super User

9 months ago

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
Power Participant
9 months ago
Hi,
I got this error when I tried:

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

Poojara_D12

Super User

9 months ago

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
Power Participant
9 months ago
Hi, when I tried your solution I got this:
When I open the table I have every column in the database listed

v-saisrao-msft
Community Support
9 months ago
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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

Forum Discussion

Convert DAX to M Code

14 Replies

Related content

Convert Dax to M Code

DAX code issue

Dax code optimization

Convert DAX to M Code

DAX coding