Forum Discussion

jmpmolegraaf's avatar
jmpmolegraaf
Frequent Visitor
1 year ago

Measure only showing values when column equals a dedicated text

Hello all,

 

thanks in advance for your time to help me as I got stuck after attempting various "solutions"

I have the following visual table:

 



and these three measures:

UMlnbv =

IF(
    NOT ISBLANK([CVlnbv]),
    CALCULATE(
        LASTNONBLANKVALUE(
            UpstreamMargins[Attribute],
            SUM(UpstreamMargins[Value])
            ),
        _Calendar[Date] <= MAX(_Calendar[Date])
    )
)

CVlnbv =
CALCULATE(
    LASTNONBLANKVALUE(
        PurchasePrices[ApplDate],
        SUM(PurchasePrices[CostValue (USD/HL)])
        ),
    _Calendar[Date] <= MAX(_Calendar[Date])
)

 

CV-UM = [CVlnbv] - [UMlnbv]

basically, I have the following needs:
- I only want to see a value for [UMlnbv] when CostCategory = "ADD" (it's a calculated column in the table PurchasePrices)
- the measure [CV-UM] should only deduct by the [UMlnbv] when CostCategory = "ADD"


please note that we do not have values for every date, hence me using lastnonblankvalue.

could someone help me? 🙂
thanks in advance!



 

 

Need Help

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    jmpmolegraaf , Try using

     

    This measure will only return a value when CostCategory is "ADD".
    DAX
    UMlnbv =
    IF(
    NOT ISBLANK([CVlnbv]) && MAX(PurchasePrices[CostCategory]) = "ADD",
    CALCULATE(
    LASTNONBLANKVALUE(
    UpstreamMargins[Attribute],
    SUM(UpstreamMargins[Value])
    ),
    _Calendar[Date] <= MAX(_Calendar[Date])

     

    CV-UM: This measure will only deduct by the UMlnbv when CostCategory is "ADD".
    DAX
    CV-UM =
    IF(
    MAX(PurchasePrices[CostCategory]) = "ADD",
    [CVlnbv] - [UMlnbv],
    [CVlnbv]
    )


    These modifications ensure that the UMlnbv measure only shows values when the CostCategory is "ADD" and that the CV-UM measure only performs the deduction when the CostCategory is "ADD"

    • jmpmolegraaf's avatar
      jmpmolegraaf
      Frequent Visitor
      1 year ago

      dear bhanu,

      yes i tried that before, see below the effect of the "TEST" measure you gave.
      basically it does not generate "lastnonblankvalues", i.e. it does not fill down the value anymore.
      it only shows a value where there is actually a value on the exact date, in the table PurchasePrices.
      for the subsequent dates, there is no entry in PurchasePrices but still need to see the value.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi jmpmolegraaf 

     

    Please try this:

    UMlnbv =
VAR _outcome =
    IF (
        NOT ISBLANK ( [CVlnbv] ),
        CALCULATE (
            LASTNONBLANKVALUE ( UpstreamMargins[Attribute], SUM ( UpstreamMargins[Value] ) ),
            _Calendar[Date] <= MAX ( _Calendar[Date] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( PurchasePrices[CostCategory] ) = "ADD", _outcome )

    CV-UM =
IF (
    SELECTEDVALUE ( PurchasePrices[CostCategory] ) = "ADD",
    [CVlnbv] - [UMlnbv]
)

     

    Hope this can help,

     

    Best Regards

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