SUM Column but retrieve a value from another table if (blank) value

[owallis]

Hi All,

I'm trying to SUM a column in table1 but if the value is (blank) then I want to use the value from table1 based on date, prefix (the other columns). There is no direct relationship between the two tables but are proxy linked through prefix and date tables

Prefix and Date table are pulled in from an external source and then linked so it is possible to slice/filter mutliple tables. 

I feel this should be easily do-able but must be missing a trick with relationships. 

Any suggestions will be welcomed

Many thanks,

Oliver

[Icey · Accepted solution]

Hi @owallis ,

How about create a "Value 2" column in "Forecast" table?

Value 2 = 
VAR PlanValue =
    LOOKUPVALUE (
        Plan[Value],
        Plan[Date], Forecast[Date],
        Plan[Prefix], Forecast[Prefix]
    )
RETURN
    IF ( ISBLANK ( Forecast[Value] ), PlanValue, Forecast[Value] )

Or, modified @mahoneypat 's measure like so:

NewMeasure =
VAR __planvalue =
    SUM ( Plan[Value] )
VAR __forecastvalue =
    CALCULATE (
        SUM ( Forecast[Value] ),
        TREATAS ( VALUES ( Plan[Date] ), Forecast[Date] ),
        TREATAS ( VALUES ( Plan[Prefix] ), Forecast[Prefix] )
    )
RETURN
    IF ( ISBLANK ( __forecastvalue ), __planvalue, __forecastvalue )

Measure 2 =
IF (
    HASONEVALUE ( 'Date'[Date] ),
    [NewMeasure],
    SUMX ( 'Forecast', [NewMeasure] )
)

BTW, .pbix file attached.

Best Regards,

Icey

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

[mahoneypat]

Not exactly sure what you are trying to accomplish but suspect you can get there with an approach like this.

NewMeasure =
VAR __planvalue =
SUM ( Plan[Value] )
VAR __forecastvalue =
CALCULATE (
SUM ( Forecast[Value] ),
TREATAS ( VALUES ( Plan[Date] ), Forecast[Date] ),
TREATAS ( VALUES ( Plan[Prefix] ), Forecast[Prefix] )
)
RETURN
IF ( ISBLANK ( __planvalue ), __forecastvalue, __planvalue )

I would also suggest you simplify your model if possible.  You can likely combine your Forecast and Actuals tables.  Simple model, simple DAX.

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

Regards,

Pat