Min give error due to blank or empty value

[Syndicate_Admin]

Hello, I have follow measure, that works well with most part of values, but it shows error in some cases.

if(values(Mastermaterialsap[DChain-spec.])="E51",Measurement[Total months available],min(MIN(min(SEM1,SEM2),MIN(SEM3,SEM4)),SEMAF2))

I suspect that the cases when gives an error is because the result of variable SEM1,SEM2,SEM3 or SEM4 is empty or blank. Can you help me to solve that in case error in the measure if gives 0, or to correct the MIN part of the measure?

many thanks

sonia

[Syndicate_Admin · Accepted solution]

Finally if it has worked. Thanks a lot

[mahenkj2]

Hi @Syndicate_Admin ,

The description of error "A table of several values was provided where a single value was expected." indicate that sometime result of one of your condition of if statement is retruning more than one value in evaluating expression, and as you say it is not happening all the time but with certain values/data, then can you please look at that data for both condition of your if statement. Else please share some data sample here.

Hope it helps!

[mahenkj2]

@

[Syndicate_Admin]

Hello, no. It has not yet been resolved. I've tried the indicated but it doesn't work. Any more ideas? Thank you

[Ahmedx]

pls try this

=If(values(Mastermaterialsap[DChain-spec.])="E51",
Measurement[Total months available],
MINX(
{ SEM1,SEM2,SEM3,SEM4,SEMAF2},[Value])
)

if this does not help you, then we need to look at your file

[Syndicate_Admin]

Hello the data type is the same. It happens that with most row elements it represents the value well. It only gives error in some elements and that is why when all the elements are selected it gives this error

Error Message:
MdxScript(Model) (711, 6) Calculation error in measurement 'Measures'[Semaphore 5monthsATW/CM]: A table of several values was provided where a single value was expected.

and I get the feeling that it gives conflict in the values that do not have data in some months, and therefore the MIN does not calculate well.

Thank you

sonia

[Ahmedx]

I think you have an error because the type of data you get with measure is different but this power bi doesn't support.
write like this

= if(
     values(Mastermaterialsap[DChain-spec.])="E51",
            Measurement[Totalmonthsavailable]&"",
                   min(MIN(min(SEM1,SEM2),MIN(SEM3,SEM4)),SEMAF2)&""
)

[Syndicate_Admin]

Hello thank you very much. If you already try if error and coalesce, but it does not work, the representation in an object continues to fail for any of the values. Thank you

[DOLEARY85]

Hi,

if i've understood correctly you want to replace the error with 0.

you could try:

IFERROR( Your measure , 0)

or

COALESCE( Your measure , 0 )

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍