i具有以下措施,总计TotalGLDetail
,表格上有许多过滤器,最后一个是问题。这些过滤器依赖的所有措施均不BLANK
,除非有时[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]))
在某些情况下,MFControl
度量为BLANK
,因为它不适用。因此,我需要此过滤器仅在MFControl
的值为NOT BLANK
时才应用。
除了以下内容以外,还有一种简洁的方式吗?
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])
)
您可以在过滤器内添加多个条件,并且可以使用最后一个条件。
TotalMF :=
CALCULATE([TotalGLDetail],
FILTER(GLDETAIL,
GLDETAIL[concat] = [AnyMFAcct] &&
(GLDETAIL[journalid] = [MFSrc1] || GLDETAIL[journalid] = [MFSrc2]) &&
ABS('GLDETAIL'[postingamount]) > [MFMinABS] &&
(ISBLANK([MFControl]) || GLDETAIL[control] = [MFControl])))