Really complicated M if/then algorithm - I desperately need it in DAX - anyone? :)

[grggmrtn]

So - I'm having issues with a slow server, and my query is caput. So I'm trying to go 100% star schema, all calculations/merging/heavy lifting in DAX, like most recommend to up performance.

I have the whole thing running, except for the very last column, which is several shades of hellish.

So - in M I created two index columns, merged them so that some of my columns had a "Previous value" column in the same row. My code is this:

    #"Tilføj Afregning" = Table.AddColumn(#"Udvidet Previous", "Afregning", each
    let minAfregning = 
    (
        if
        [CPR] = [Previous.CPR] and
        [Indsats navn] = [Previous.Indsats navn] and 
        [Leverandør] = [Previous.Leverandør] and 
        [Dato] = [Previous.Dato] +1 and 
        [Dag] = "Tuesday" and 
        [Previous.Dag] = "Monday" and 
        [Previous.Dato] <> [Visitation **bleep**] and 
        (
            (
                [Pause] = 0 and 
                [Previous.Pause] = 0
            )
            or 
            (
                [Pause] = 0 and
                [Previous.Pause] = 1
            )
            or 
            (
                [Pause] = 1 and 
                [Previous.Pause] = 0
            )
        )
        then
        1
        else
        0
    )
    in
    (
        if (
            [Indsats navn] = "R1 - Rengøring*" or
            [Indsats navn] = "VP - Tøjvask*"
           )
        and
        [Frekvens] = 1
        then 
        minAfregning * 2
        else
        minAfregning
    )

It's complicated, but necessary.

My problem is, when trying to do the same thing in DAX (without the "Previous" columns) I crash my computer 🙂

Instead of the "previous" columns I've created VAR's for each of them. For example:

VAR PreviousCPR =
CALCULATE(
    MAX('Dim_Borger'[CPR]),
    FILTER(ALL('Fact_PlanlagtVisiteretTid'),
    'Fact_PlanlagtVisiteretTid'[Indeks]=EARLIER('Fact_PlanlagtVisiteretTid'[Indeks])-1)
    )

And I add the rules for the pauses thus:

VAR PauseRegel1 =
IF(
    'Fact_PlanlagtVisiteretTid'[Pause] = 0 && PreviousPause = 0,
    7,
    8
)

VAR PauseRegel2 =
IF(
    'Fact_PlanlagtVisiteretTid'[Pause] = 0 && PreviousPause = 1,
    7,
    8
)

VAR PauseRegel3 =
IF(
    'Fact_PlanlagtVisiteretTid'[Pause] = 1 && PreviousPause = 0,
    7,
    8
)

VAR PauseRegler =
IF(
    PauseRegel1 = 7 || PauseRegel2 = 7 || PauseRegel3 = 7,
    7,
    8
)

And once I've got all the VARs in place I do this (it's probably VERY wrong, so brace yourself):

VAR minAfregning =
IF(
    RELATED('Dim_Borger'[CPR]) = PreviousCPR &&
    RELATED('Dim_Indsats'[Indsats navn]) = PreviousIndsats &&
    RELATED('Dim_Leverandor'[Leverandør]) = PreviousLeverandoer &&
    RELATED('CDim_DatoLight'[Dato]) = PreviousDato + 1 &&
    RELATED(Dato[Uge Dag]) = "Tuesday" &&
    ForrigDag = "Monday" &&
    ForrigDato <> RELATED('Dim_IndsatsDetaljer'[VisitationSlut]) &&
    PauseRegler = 7,
    1,
    0
)

RETURN
minAfregning

And BOOM. No more RAM (I have 32 Gigs...)

I know this is REALLY complicated, but my DAX sucks compared to my M, and I really need to get this algorithm working in DAX

Any takers?

[parry2k]

@grggmrtn you explained a lot, it will be much easier if you put sample data and expected output. 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[grggmrtn]

What I can present is the following:

• There's 1 Fact table and 7 DIM tables, and 1 Date table (CALENDARAUTO() with extra columns).
• Each dimension has its key column, and the corresponding data column.
• The fact table has all dimension keys, and the [Pause] column.
• All DIM tables are related to the Fact table through their keys. Standard star.
• The expected output is in my code - 1 if all criteria are met, else 0.

I don't have the foggiest idea how to present sample data that would make sense in this particular case.

[parry2k]

@grggmrtn you can throw sample data of a few dimensions and fact in excel sheet and share the expected result. You need to help us so that we can help you otherwise not sure how we can support and assist here.

[grggmrtn]

actually @parry2k I'm gonna have to pass. I just made a quick first attempt, but I'm realizing that in order to create dummy data, it's going to have to be at least as complicated as my algorithm, since I need to present all cases. It's just too huge. 😥

I just don't know where else to go with this

[parry2k]

@grggmrtn even I don't know. as much as I want to help, but it is not straight forward if you think the logic is complex, not sure how I will able to wrap my head around based on the details you have provided. I hope someone else can help and get you over the line. 

If you end up putting sample together, just tag me and I will be more than happy to assist.

Good luck!

[grggmrtn]

I'm TOTALLY with you on that. You're just asking me to REcreate somthing that's immensly complicated and I'm not sure I know how, and still have the "expected" result. It's just frustrating

It might take a couple hours. I'll see what I can do, but seriously, no promises, I'm at the brink here