Forum Discussion

Roland74's avatar
Roland74
Icon for Helper I rankHelper I
1 year ago

DAX Measure with multiple conditions

Hi, I'm struggling to create a measure with multiple conditions. Hope you can help! I'm on direct query, can't easily change the model. I've created a sample, and the model looks like:   Th...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Roland74 

 

Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?


Best Regards,

Jayleny

  • Roland74's avatar
    Roland74
    Icon for Helper I rankHelper I
    1 year ago

    I finaly got the measure right for the row-level, however the totals are way off. Any suggestions.

     

    Measure with the right row-values for the projects:

     

    ProvisionLoss(Def) =
    ------------------- Last LineNumber in Dim-table ---------------------
    ------------------- (Posted = TRUE && PostedMonth <= SelectedMonth ) ------------------------------
    VAR MaxLineNumberSelected = 
                CALCULATE(
                    MAX(ProjectPreclosureResult[LineNumber]),
                    PostingDate[CYearMonthNo] <= SELECTEDVALUE('Date'[CYearMonthNo]),
                    ProjectPreclosureResult[Posted] = "TRUE",
                    CROSSFILTER(factProjectPreclosureResult[ProjectPreclosureResultID], ProjectPreclosureResult[ProjectPreclosureResultID], Both)
                    )

    ------------------------- Closed Project have no valua after the last ModefiedDate ------------------------------
    ------------------------- CorrectionPosted = TRUE && CorrectionPostedBy = N/A -----------------------------------
    VAR LastDateModified =
                CALCULATE(
                    MAX(DateModified[CYearMonthNo]),
                    ProjectPreclosureResult[CorrectionPosted] = "TRUE" && ProjectPreclosureResult[CorrectionPostedBy] = "N/A",
                    CROSSFILTER(factProjectPreclosureResult[ModifiedDateID], DateModified[DateID], Both)
                    )

    -------- Alternative, if LastDateModified = Blank --------
    VAR IfLastDateModifiedIsBlank = SELECTEDVALUE('Date'[CYearMonthNo]) +1

    VAR MaxDateCorrection = IF (ISBLANK(LastDateModified), IfLastDateModifiedIsBlank, LastDateModified)

    --------- Final Measure-----------------------------------------------------------------------------------------------------

    VAR ProvisionLossSelected =
                ROUND(
                CALCULATE(
                    SUMX(factProjectPreclosureResult, factProjectPreclosureResult[CorrectedProvisionLoss_PPR]),
                    ProjectPreclosureResult[LineNumber] = MaxLineNumberSelected &&
                    SELECTEDVALUE('Date'[CYearMonthNo]) < MaxDateCorrection
                    )
                ,2)

    RETURN
            IF(ProvisionLossSelected = 0, BLANK(), ProvisionLossSelected)