topic Re: Measure calculation when no items existing in the source table in DAX Commands and Tips

Measure calculation when no items existing in the source table

Anonymous — Mon, 28 Feb 2022 14:50:51 GMT

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,

Re: Measure calculation when no items existing in the source table

tamerj1 — Mon, 28 Feb 2022 22:18:43 GMT

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

Re: Measure calculation when no items existing in the source table

AlexisOlson — Mon, 28 Feb 2022 21:31:53 GMT

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.

Re: Measure calculation when no items existing in the source table

v-easonf-msft — Mon, 07 Mar 2022 11:39:27 GMT

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