Error multiple value when a single value was expected

[RomainBSNP]

Hello everyone, first I have to thanks anyone in this forum for sharing and helping people, it's always nice to have some support.

I spend a lot of times on this forum (and many else to be honest) without finding an answer at my problem. Even Chat GPT can't help me on this one !

So i will try to explain it at the best I can :

I'm creating a powerbi report to calculate stock values and depreciation, regarding different parameters.

Basicly, I have "3" important tables :

- Lot_batch_master : it's the table where I can find the condition code of a part ; the quantity in stock ; the creation date of a part and the part number itself (designation)

- Inventory_part_unit_cost : the table where I can find the inventory_value, which is the price per part_no

- Inventory_part : where I can find the hazard code (used to define the part, especially usefull for depreciation calculations)

My main table is Lot_batch_master, and the common connexion is the "part_no" field. I was forced to put this relation "Many to Many", and I can't figure how to do it in another way. Tried to remove errors, blank, null and duplicates for every part_no in every table doesn't help. I also already checked : the data type is correct (text).

I forgot to mention, I also have a date table linked to the creation date of part (lot_batch_master_tab).

My main matrix look like that : (VALEUR STOCK BRUT is a mesure I createdn multiplying sum of inventory_value per sum_of_received_qty). PS : it's for testing purpose, none of these values are real or accurate at the moment.

So my main problem is when I try to create on a custom table a mesure to calculate a depreciation regarding condition code.

I'm currently using this dax formula :

ConditionCodeBasedDepreciation =

VAR ConditionCode = VALUES('Lot_Batch_Master_tab'[CONDITION_CODE])

VAR AgeInDays = DATEDIFF(MAX('Lot_Batch_Master_tab'[CREATE_DATE]), TODAY(), DAY)

RETURN

    CALCULATE (

        IF (

            ConditionCode = "second",

            SWITCH (

                TRUE(),

                AgeInDays <= 90, 0.50,

                AgeInDays <= 180, 0.25,

                0

            ),

            0

        ),

        'LOT_BATCH_MASTER_TAB'

    )

The formula is accepted in my matrix, but the result can't be displayed : 

I'm out of idea right now, tried a lot of possibilites.

Any help would be greatly appreciated.

Regards,

[RomainBSNP]

In short : Part BEBIW1195C2 the "VALEUR STOCK BRUT" should be multiplied by ;

0.50 when the part is in stock from 90days or less (the case we have in my sample above)

0.25 when the part is in stock for more than 91 days but less than 180 days

this only IF the hazard code is SECOND for the part.

In the example above, we should get 117,03 * 0.50 = 58,15

I tried to create a new measure using the formula you give me above, can't figure which one can be correct...

[Ahmedx]

it should be roughly like this

* max ('Lot_Batch_Master_tab'[VALEUR STOCK BrUT])

VAR ConditionCode = max('Lot_Batch_Master_tab'[CONDITION_CODE])
VAR AgeInDays = DATEDIFF(MAX('Lot_Batch_Master_tab'[CREATE_DATE]), TODAY(), DAY)
RETURN
    CALCULATE (
        IF (
            ConditionCode = "second",
            SWITCH (
                TRUE(),
                AgeInDays <= 90, 0.50,
                AgeInDays <= 180, 0.25,
                0
            ),
            0
        ),
        'LOT_BATCH_MASTER_TAB'
    )* max ('Lot_Batch_Master_tab'[VALEUR STOCK BrUT])

[RomainBSNP]

Hello Ahmedx, thanks again for the research !
With many testing I forgot to add the multiplication on my last formula, I apologize. It still does not work unfortunately.

I Also tested with your latest post, It does not allow calculation from "Valeur stock brut", which is a custom mesure not coming from any "core" table. Valeur stock brut comes from "Mesure" table.

Lot_batch_master_tab is the table containing condition_code, the part_no and received_qty.

[Ahmedx]

well, to help you I need to get the file

[RomainBSNP]

How should I process in this forum ? The pbix file type is not supported for sharing right here.

[Ahmedx]

LOT_BATCH_MASTER_TAB'

then what is it?
and where is the multiplication operation in your formula?

[Ahmedx]

pls try this

ConditionCodeBasedDepreciation =
VAR ConditionCode = max('Lot_Batch_Master_tab'[CONDITION_CODE])
VAR AgeInDays = DATEDIFF(MAX('Lot_Batch_Master_tab'[CREATE_DATE]), TODAY(), DAY)
RETURN
    CALCULATE (
        IF (
            ConditionCode = "second",
            SWITCH (
                TRUE(),
                AgeInDays <= 90, 0.50,
                AgeInDays <= 180, 0.25,
                0
            ),
            0
        ),
        'LOT_BATCH_MASTER_TAB'
    )

[RomainBSNP]

Hello @Ahmedx , thank you for the very quick answer !

At least now it is displaying a result, but not the one intended :

I wasn't clear enough, the formula should be based on : check if the part_no has a condition_code "SECOND", if yes => multiply the valeur_stock_brut per the depreciation regarding the age of the part (90 days or less, multiply the valeur_stock_brut per 0.50.... and so on)

Regards,

[Ahmedx]

I don’t understand what you want to calculate, I need to get the file and find out the desired result so that I can help you