Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Qootaro
New Member

How to calculate with criteria contains filter in Measure

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.

 

Qootaro_0-1738044268270.png

 

Please give suggestion to solve problem.

3 REPLIES 3
some_bih
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

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)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






@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.

 

IMG_7624.jpeg

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.