How to calculate with criteria contains filter in Measure

[Qootaro]

I found problem on calculation with grouping filter depend on table name

The expected result of calculation separate into 2 groups depend on table name containing condition. And both have similar calculation but different only filter.

- Table Transaction need sum all in period (example monthly)

- Table Profile need pick record at max date only

Here is my code

VAR Table_Type = CONTAINS('Log Result','Log Result'[table_name],"txn")

RETURN

ROUNDDOWN(

    CALCULATE(

        IF(Table_Type,

            //Transaction Table

            1-DIVIDE(

                CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt")),

                CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"))

            )

            ,

            //Profile Table

            1-DIVIDE(

                CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = MAX('Log Result'[pos_dt]))),

                CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = MAX('Log Result'[pos_dt])))

            )

        )

    )

,5)

By the way the result on display table as below attached, expectation result must be 99.643% at the first row that is one part of "txn" table. But actually result shown 99.641% that came from record at max date.

Please give suggestion to solve problem.

[some_bih]

Hi @Qootaro without model it is hard to spot issue. Did you check that you have value as in named FILTER function for that column marked red?

[some_bih]

Hi @Qootaro try code below (adjust VAR for max)

VAR Table_Type = CONTAINS('Log Result','Log Result'[table_name],"txn")
VAR __max_var=MAX('Log Result'[pos_dt])

RETURN

ROUNDDOWN(

CALCULATE(

IF(Table_Type,

//Transaction Table

1-DIVIDE(

CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt")),

CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"))

)

,

//Profile Table

1-DIVIDE(

CALCULATE(SUM('Log Result'[Value]), FILTER('Log Result','Log Result'[Condition] = "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = __max_var)),

CALCULATE(SUM('Log Result'[validated_total_row]), FILTER('Log Result','Log Result'[Condition] <> "failed_record_count_excpt"), FILTER('Log Result','Log Result'[pos_dt] = __max_var))

)

)

)

,5)

[Qootaro]

@some_bih thank you, i tried that but the result did not change.

Expectation for table which contain "txn" must calculate by row 10 but actual run thru at row 16. I don't know why and no idea with this mechanism.