Measure only showing values when column equals a dedicated text

[jmpmolegraaf]

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!

[Anonymous]

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.

[bhanu_gautam]

@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]

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.