Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculated column looking up for specifc data

Good day all,   Hope someone can help me with the following As you can see, Box A is linked to 2 types of sales (W & P) and 2 customers (1 & 2) I want to add a calculated column.  The formula mu...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Column =
VAR Customer = test[adres_cod]
VAR BOX = test[container_nr]
VAR Sales = "PTI&OFFH"
RETURN
    CALCULATE (
        FIRSTNONBLANK ( test[adres_cod], 1 ),
        test[container_nr] = BOX,
        test[occ_act_group] = Sales,
        ALL ( test )
    )

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Good day,

In my table, a container_nr is mentioned several times. This is oke for me.

However, the container_nr can be mentioned with different adres_codes and different occ_act_groups. And that is what I don’t want😉

My goal is to see the container_nr mentioned in the table, but with only 1 adres_cod in the calculated column, not 2.

The proper adres_cod is always the one which is combined with the occ_act_group PTI & OFFH.

I hope this helps to give me an advise!

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Please modify your formula as below:

Column =
VAR Customer = test[adres_cod]
VAR BOX = test[container_nr]
VAR Sales = "PTI&OFFH"
RETURN
    CALCULATE (
        VALUES ( test[adres_cod] ),
        test[container_nr] = BOX,
        test[occ_act_group] = Sales,
        ALL ( test )
    )

 

Best regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Good day all,

     

    The formula works fine now, thanks for that!

     

    I have one last challenge.

    Below you see container_nr 'B' with 2 different adres_code (KRA588 and KRA001). with both, the occ_act_group is 'PTI & OFFH'.

    The formula now gives me an error.

     

    So, in this case, I want to mention the first adres_cod in the calculated column (in this case KRA588).

     

    Does anyone have a solution for this?

     

    Thanks upfront for your kind assistance in this!

     

    John

     

     

     

     

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Column =
VAR Customer = test[adres_cod]
VAR BOX = test[container_nr]
VAR Sales = "PTI&OFFH"
RETURN
    CALCULATE (
        FIRSTNONBLANK ( test[adres_cod], 1 ),
        test[container_nr] = BOX,
        test[occ_act_group] = Sales,
        ALL ( test )
    )

    Best regards,

    Yuliana Gu