Forum Discussion

OnlyPhilip's avatar
OnlyPhilip
Frequent Visitor
6 years ago
Solved

TREATAS not picking a single value

Hi everybody, I need your help for this apparently simple problem.

 SUMX(Batch,
        CALCULATE(
	    MAX(MetalValues[Value]),
            TREATAS(VALUES(Batch[Metal]), MetalValues[Metal]),
            TREATAS(VALUES(Batch[Date]), MetalValues[Date])
        )
 )

The Batch table contains a list of production Dates of different Metals. I need to retrieve the Price for those dates and metals from the unrelated MetalValues table and sum them up.
The issue is that, at each iteration of SUMX, the MAX function returns the max value of the whole MetalValues table. My expectation and need is that it only returned the MetalValue value from that Date and Metal (which is unique being a daily trading value).
Any idea on what's my mistake here?
Thanks
Fil

 

  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Please try this measure expression instead

     

    New Measure =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE ( Batch, Batch[Metal], Batch[Date] ),
        "@value",
        VAR thisdate = Batch[Metal]
        VAR thismetal = Batch[Metal]
        RETURN
            CALCULATE (
                MAX ( MetalValue[Value] ),
                MetalValue[Metal] = thismetal,
                MetalValue[Date] = thisdate
            )
    )
RETURN
    SUMX ( summary, [@value] )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    OnlyPhilip ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
    Appreciate your Kudos.

  • OnlyPhilip's avatar
    OnlyPhilip
    Frequent Visitor
    6 years ago

    Thanks for your answers. The original pbix contains some confidential data, I'm attaching  a screenshot of the two tables, hope it helps. The first one is the Batch table, the second it MetalValues, where the values should be retrieved from.

    I thought maybe there was a mismatch in the Date field, but they both appear formatted as Dates.

    Thanks!

     

    Batch

     

    MetalValues

     

     

     

     

     

     

     

     

    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Please try this measure expression instead

       

      New Measure =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE ( Batch, Batch[Metal], Batch[Date] ),
        "@value",
        VAR thisdate = Batch[Metal]
        VAR thismetal = Batch[Metal]
        RETURN
            CALCULATE (
                MAX ( MetalValue[Value] ),
                MetalValue[Metal] = thismetal,
                MetalValue[Date] = thisdate
            )
    )
RETURN
    SUMX ( summary, [@value] )

       

      If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

      Regards,

      Pat