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]), ...
  • 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

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