Forum Discussion

RobX's avatar
RobX
Advocate I
5 years ago

If Statement With Two Tables

I have two tables with a one to many relationship.  I'm trying to create a measure display true/false if the summed value in one table is greater than the summed value in the other table.

 

I tried this measure but it's duplicating my records.  Does anyone know how to correct this?

 

TrueFalse=
IF(SUM(TableA[Amount]) < SUM(TableB[Amount]), "True", "False")

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Very hard to understand the requirements without sample data and expected results, and there is no indication of what the evaulation context is here. Sample data would be great. Without that, the only thing I can suggest is wrap both SUM() functions with CALCULATE() and see if the context transition gets you what you need. If not, provide data please and expected results.

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

    • RobX's avatar
      RobX
      Advocate I
      5 years ago

      Here's a sample.

       

      TableA

       

       

      TableB

       

       

      I want to create the table below with the measure "A Less Than B"

       

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Amount A shoud not be an implict measure. So make Amount A be a measure like
    Total Amount A = SUM(Table[Amount A])

    Same for Amount B.

    Then use 

    IF([Total for Amount A] < [Total for Amount B], "True", "False")

    as your measure. That is really the same as wrapping your SUM()s above with a CALCULATE.

     

    If you want actual tested code, please provide data per the links below. I cannot copy your images in to Power BI. 

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.