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 morning,

 

Unfortunately, the calculation is not working as good as I thought.

Can you please help me, so the info which I mentioned manually is automatically calculated?

Thanks!

John

 

 

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