CODEWITHSUNDEEP
dax
John Donnelly
John Donnelly

Reputation: 917

DAX - Apply Filter If Measure Used In Filter is Not BLANK

I have the following measure totaling up TotalGLDetail with a number of filters on the table, the last one being the issue. All the measures these filters depend on are never BLANK except, sometimes, [MFControl]

TotalMF:=CALCULATE([TotalGLDetail]
    ,FILTER(GLDETAIL,GLDETAIL[concat]=[AnyMFAcct])
    ,FILTER(GLDETAIL,OR(GLDETAIL[journalid]=[MFSrc1],GLDETAIL[journalid]=[MFSrc2]))
    ,FILTER(GLDETAIL,ABS('GLDETAIL'[postingamount])>[MFMinABS])
    ,FILTER('GLDETAIL',GLDETAIL[control]=[MFControl]))

In some cases, the MFControl measure is BLANK because it does not apply. So I need this filter to only apply if the value of MFControl is NOT BLANK.

Is there a concise way to write this other than the following? 

TotalMF:=IF(NOT(ISBLANK([MFControl]))
    ,CALCULATE([TotalGLDetail]
        ,FILTER(GLDETAIL,GLDETAIL[concat]=[AnyMFAcct])
        ,FILTER(GLDETAIL,OR(GLDETAIL[journalid]=[MFSrc1],GLDETAIL[journalid]=[MFSrc2]))
        ,FILTER(GLDETAIL,ABS('GLDETAIL'[postingamount])>[MFMinABS])
        ,FILTER('GLDETAIL',GLDETAIL[control]=[MFControl])),CALCULATE([TotalGLDetail]
        ,FILTER(GLDETAIL,GLDETAIL[concat]=[AnyMFAcct])
        ,FILTER(GLDETAIL,OR(GLDETAIL[journalid]=[MFSrc1],GLDETAIL[journalid]=[MFSrc2]))
        ,FILTER(GLDETAIL,ABS('GLDETAIL'[postingamount])>[MFMinABS])
)

Upvotes: 0

Views: 7710

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You can add more than one condition inside of a filter and you can use an or condition for the last one.

TotalMF := 
    CALCULATE([TotalGLDetail],
              FILTER(GLDETAIL,
                     GLDETAIL[concat] = [AnyMFAcct] &&
                     (GLDETAIL[journalid] = [MFSrc1] || GLDETAIL[journalid] = [MFSrc2]) &&
                      ABS('GLDETAIL'[postingamount]) > [MFMinABS] &&
                      (ISBLANK([MFControl]) || GLDETAIL[control] = [MFControl])))

Upvotes: 1

Related Questions