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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Do you want this as a calculated field for a calculated column?  Also, what is the logic there?  Is it that for a certain container_nr, if the term PTI & OFFH is found, then show the value in the adres_cod column corresponding to the term PTI & OFFH?  Pleae clarify.

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