A function "FILTER" has been used in a True/False expression

[Anonymous]

Hi, 

Need help on this formula. Thought I was doing the DAX correctly.. but got an "error" - A function "FILTER" has been used in a True/False expression .

Any suggestion on the correct fomula. Thanks a bunch. 

I want to calculate the cumulative progress with an IF condition. 

Measure:
RL **bleep** CP Prog =

CALCULATE(

SUMX('09 CP filter', '09 CP filter'[RL_CP]),

IF('09 CP filter'[Field]="P",

AND(

FILTER(ALL('09 CP filter'), SUMX(FILTER('09 CP filter', EARLIER('09 CP filter'[ID])='09 CP filter'[ID]), '09 CP filter'[RL_CP])),

FILTER(ALLEXCEPT('09 CP filter', '09 CP filter'[Tag]), '09 CP filter'[Overall Code]<=MAX('09 CP filter'[Overall Code]))),

FILTER(

ALLEXCEPT('09 CP filter', '09 CP filter'[Tag]), '09 CP filter'[Overall Code]<=MAX('09 CP filter'[Overall Code]))

))

Thanks.

[v-luwang-msft · Accepted solution]

Hi @Anonymous 

You could try the following measure:

RL **bleep** CP Prog2 =
VAR test1 =
    CALCULATE (
        SUMX ( '09 CP filter', '09 CP filter'[RL_CP] ),
        FILTER (
            ALL ( '09 CP filter' ),
            SUMX (
                FILTER ( '09 CP filter', EARLIER ( '09 CP filter'[ID] ) = '09 CP filter'[ID] ),
                '09 CP filter'[RL_CP]
            )
        ),
        FILTER (
            ALLEXCEPT ( '09 CP filter', '09 CP filter'[Tag] ),
            '09 CP filter'[Overall Code] <= MAX ( '09 CP filter'[Overall Code] )
        )
    )
VAR test2 =
    CALCULATE (
        SUMX ( '09 CP filter', '09 CP filter'[RL_CP] ),
        FILTER (
            ALLEXCEPT ( '09 CP filter', '09 CP filter'[Tag] ),
            '09 CP filter'[Overall Code] <= MAX ( '09 CP filter'[Overall Code] )
        )
    )
VAR field =
    CALCULATE (
        MAX ( '09 CP filter'[Field] ),
        FILTER (
            ALL ( '09 CP filter' ),
            '09 CP filter'[ID] = MAX ( '09 CP filter'[ID] )
                && '09 CP filter'[Field] = MAX ( '09 CP filter'[Field] )
        )
    )
VAR test3 =
    IF ( field = "P", test1, test2 )
RETURN
    test3

Hope it is helpful for you!

Best Regard

Lucien Wang

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak , sorry how to attach a file? 

[amitchandak]

@Anonymous , Upload to onedrive or dropbox and share the link

[Anonymous]

Hi @amitchandak , Sorry I think I confused you a bit. I want the end result to be column N. 

Scenario, if column F is filtered with "P", although column E are the same, cumulative progress should be calculated by grouped of column E and column K with max of column L., else, just calculate cumulative progress by column E and max of column L. 

[Anonymous]

@amitchandak Thanks for the info, Sorry those arent allowed in this PC.

Here is the screenshot od my data. 

Basically I want to achieve the Cumm Prog % at Column N.

Thanks again. 

[amitchandak]

@Anonymous , You need equal columns like the 2nd column with = to join on max and incremental column like a first column or overall code

and you need a measure like

calculate([RL CP%], filter(allselected(Table), Table[Col] = max(Table[Col]) && Table[Col2] <= max(Table[Col2])))

Use correct column in place col and col2