Forum Discussion

Ole111's avatar
Ole111
Icon for Helper II rankHelper II
5 years ago

First occurrence of not empty value in one column based on given value from another column.

Hi,

The problem is following:

the table ‘Transactions’ has 4 columns: [transaction], [amount], [customerID], [profitCenter]

All the transactions have [profitCenter] but some of them do not have [customerID].

I need to split all the transactions by customer. As a simplification there  is an assumption that for blank [customerID] we can assign any of the customers appearing for a given [profitCenter] .

 So I have to find a formula returning the first occurrence of [customerID] among the transactions of given [profitCenter].

Thanks for any hint.

Need Help

15 Replies

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    Hello Ole111 

     

    If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

     

    You can send the sample .pbix file by adding it to your drive or dropbox and add the link here.

     

    Regards
    Kumail Raza

  • Ole111's avatar
    Ole111
    Icon for Helper II rankHelper II
    5 years ago

    Hello Kumail,

     

    To give a simplified picture:

    resource table:

     

    report:

     I need to distribute sales with blank customerID among existing curomers using the simplified assumption that it can be any customerID for given costCenter.

    • Kumail's avatar
      Kumail
      Icon for Impactful Individual rankImpactful Individual
      5 years ago

      Ole111 

       

      That means unassigned sales to be distributed to customers with existing sales. Right?

       

      Regards

      Kumail Raza

      • Ole111's avatar
        Ole111
        Icon for Helper II rankHelper II
        5 years ago

        Hi Kumail,

         

        Exactly. E.g. the amount 27.15 in row 3 should be assigned to first occurrence of customerID for profitCenter 319543093 (which is not on the screenshot scope, but should be in full table).

         

        Best,

        Dariusz

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Ole111 ,

     

    If I understand correctly, do you want to allocate resources corresponding to blank customers to the same profitCenter field, as shown below.

    If so, you may wish to directly group according to profitCenter, it will directly sum the resources of the same group, refer to the following:

    C =
CALCULATE (
    SUM ( Transactions[amount] ),
    FILTER (
        ALL ( Transactions ),
        Transactions[profitCenter] = MAX ( Transactions[profitCenter] )
    )
)


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Ole111's avatar
      Ole111
      Icon for Helper II rankHelper II
      5 years ago

      Hi v-henryk-mstf ,

      In fact the real need is to use a formula in a calculated comun in 'Transactions' table with customerID (original one if exists or found one - first nonblank), because it will be used later as a reference for other operations e.g. getting address (for a map view) from other lookup table with customer details. And I have to keep details from other columns for individual transactions (the real 'Transaction table have much more columns than given simplified example), so aggregation of sales won't work in this case.

      Best regards,

      Dariusz