Filter Table Created in Report View

[martipe1]

I have a Power Bi report that uses several tables, I might use merge queries to solve my specific problem but don't know if it would be the best for my model as I would be repeating data.

I created this table in report view, take note that 3rd column is a SUM from Field C in Table A and the last column is Field A from table B.

TableA.FieldA	TableA.FieldB	TableA.SUM(FieldC)	TableB.FieldA
12406	A	10	10
12406	B	20	10
12407	A	10	10
12408	A	50	20
12408	B	20	40
12409	A	30	10
12409	B	10	15
12410	A	20	15

I want to filter the table to show those records where TableA.SUM(Field C) < TableB.FieldA, to show the next table instead:

TableA.FieldA	TableA.FieldB	TableA.SUM(FieldC)	TableB.FieldA
12408	B	20	40
12409	B	10	15

I would appreciate your comments about the best approach.

Thanks.

[v-kongfanf-msft]

Hi @martipe1 ,

You can try formula like below, and then use it in "Filters on this visual".

MEASURE =
IF (
    MAX ( 'Table'[TableA.SUM(FieldC)] ) < MAX ( 'Table'[TableB.FieldA] ),
    1,
    0
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martipe1]

Thank you very much for your answer.

I do not understand your formula, would you be so kind to elaborate.

1. Why use MAX ?
2.  Don't know how to accomplish this part 

'Table'[TableA.SUM(FieldC)] 

Thanks in advance for your help

[v-kongfanf-msft · Accepted solution]

Hi @martipe1 ,

Getting the current row in measure requires wrapping it with an aggregate function. You can refer to below blog for more details:Calculated Columns and Measures in DAX - SQLBI

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.