To resolve a #MULTIVALUE error

[Former Member]

Hi everyone,

I have 4 objects in a report, say, Order_id, Unit_id, Is_Accepted (either Y or N), Count_Accepted.

I need to count number of accepted units for a particular order. But when I am using the formula:

Count_Accepted=If ([Is_Accepted])="Y" Then Count([Unit_id])   ,

I am getting a for all those orders which have both accepted & unaccepted units for them, unlike those orders which have either all Y values or all N values of Is_Accepted for all the Unit_id.

I have tried ForEach & ForAll and also feel aggregation isn't the key here.

So can anyone provide a resolution for this error ?

Much thanks in advance.

[Former Member · Accepted solution]

Hi Nina,

Try creating table using below columns

OrderID

Count([OrderID];All) Where ([Is_Accepted]="N")

Count([OrderID];All) Where ([Is_Accepted]="Y")

[Former Member · Accepted solution]

Hi Divya,

What a witty formula ! Counting Order_Id instead of Unit_id makes all the difference.Thanks a lot

for the help !

Cheers !

Nina

[Former Member]

Hello Nina,

Please use this

=Sum((If [Is_Accepted]="Y" Then 1 Else 0) In ([order_ID];[Unit_ID]))

Regards

niraj

[Former Member]

Hi Niraj,

Thanks for your quick response, this indeed helped, but it displays two records for each of the multivalued orders.

I have to calculate an Is_Not_Accepted and a Difference(Accepted-Not_Accepted) measure as well, so two records per order is looking a bit cumbersome.

Any way to overcome this ? Thanks in advance !

[Former Member]

Hello Nina,

Could you please share how you want to display the data so as to apply appropriate approach?

Regards

Niraj