Measure calculation when no items existing in the source table

[Anonymous]

Hi all,

I'm trying to build a measure that compute a value depending on a field from the table.

Here is my model

Here are the datas.

In Dax Studio I have the following measure definition.

DEFINE

MEASURE TBSource[sum1]=
	var result = CALCULATE(sum(TBSource[Quantity]))
	RETURN IF (ISBLANK(result),0, IF(result< 0,0,result))

var tb = SUMMARIZECOLUMNS(
	TBName[Name],
	TBSource[Code],
	"sum1", [sum1]
	)


EVALUATE tb

the result is the following

What I try to do is, when C3=0, then I want to change the quantity to 5.

The problem is that no datas exists for this code and the Name AA. How can I do that ? And I do not want to have this rule applied to C2, wich has also no data for the name BB.

Thanks for your help,

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

You can change you formula as below:

DEFINE
    MEASURE TBSource[sum1] =
        VAR result =
            CALCULATE ( SUM ( TBSource[Quantity] ) )
        RETURN
            IF ( ISBLANK ( result ), 0, IF ( result < 0, 0, result ) )
    VAR tb =
        ADDCOLUMNS (
            SUMMARIZECOLUMNS ( TBName[Name], TBSource[Code] ),
            "sum1",
                IF ( TBSource[Code] = "C3" && TBSource[sum1] = 0, 5, TBSource[sum1] )
        )

EVALUATE
tb

Best Regards,
Community Support Team _ Eason

[AlexisOlson]

It sounds like your data is incomplete. I'd recommend appending a new row to your TBSource table before loading it into your model rather than trying to add data via measures.

[tamerj1]

@Anonymous 

Not sure about data lineage but you may try

sum1 =
VAR tb1 =
SUMMARIZE ( TBName[Name], TBSource[Code], "sum1", SUM ( TBSource[Quantity] ) )
VAR tb2 =
ADDCOLUMNS (
tb1,
"@sum1",
SWITCH (
TRUE,
[Code] = "C3"
&& [sum1] = 0, 5,
ISBLANK ( [sum1] ), 0,
[sum1] < 0, 0,
[sum1]
)
)
RETURN
SUMX ( tb2, CALCULATE ( SUM ( [@sum1] ) ) )