Forum Discussion

martipe1's avatar
martipe1
Helper II
2 years ago
Solved

Filter Table Created in Report View

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.FieldATableA.FieldBTableA.SUM(FieldC)TableB.FieldA
12406A1010
12406B2010
12407A1010
12408A5020
12408B2040
12409A3010
12409B1015
12410A2015

 

 

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

 

TableA.FieldATableA.FieldBTableA.SUM(FieldC)TableB.FieldA
12408B2040
12409B1015

 

I would appreciate your comments about the best approach.

 

Thanks.

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      martipe1
      Helper II
      2 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.