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

[Ole111]

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.

[v-henryk-mstf]

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]

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

[Ole111]

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]

@Ole111 

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

Regards

Kumail Raza

[Ole111]

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

[Kumail]

@Ole111 

Alright. I would be able to provide you with the working solution, once sample.pbix file is available.

Regards

Kumail Raza

[Ole111]

@Kumail 

Here you go:

https://www.dropbox.com/s/zb7bj0vmtb0kr1l/Transactions.pbix?dl=0

[Kumail]

@Ole111 

The file is given in the URL below for your reference.

https://drive.google.com/file/d/1_zwvLRf3O97iHqWeM2enfw5m_bhUo1QQ/view?usp=sharing

Regards

Kumail Raza

Did this help? Kudos are appreciated.

Consider Accept it as the solution to help the other members find it more quickly.

[Kumail]

@Ole111 

Have you got the solution from the file shared in the above URL?

Regards

Kumail Raza

Did this help? Kudos are appreciated.

Consider Accept it as the solution to help the other members find it more quickly.

[Ole111]

Hi @Kumail

That is almost it. The one thing to deal with is that in your solution it assigns the maximum customerId number:

customerIDAdj =

VAR _ProfitCenter = Transactions[profitCenter]

Return

If (Transactions[customerID] = BLANK(),

CALCULATE(MAX(Transactions[customerID]), FILTER(Transactions, Transactions[profitCenter] = _ProfitCenter)),

Transactions[customerID])

While it is expected to be the first occurence of customerID for given profitCenter.

Best regrds,

Dariusz

[Kumail]

Hi @Ole111 

Can you share a specific example from the shared .pbix file? 

This will help better understand.

Regards

Kumail Raza

Did this help? Kudos are appreciated.

Consider Accept it as the solution to help the other members find it more quickly.

[Ole111]

Hi @Kumail 

I added new transactions with more CustommerID to ilustrate it better.

You can see that using MAX formula it assignes circled CustomerID while it should be highlighted one - as first encountered:

The modified example file is here:

https://www.dropbox.com/s/cqhp43kvq72n4xr/Transactions%20%281%29.pbix?dl=0

Best regards,

Dariusz

[Kumail]

@Ole111 

The business logic needs to be updated by the following scenarios;

-The sorting of the customerId is changed by the user in the table. 

-There are no values after the blank cell in the table. 

Regards

Kumail Raza

Did this help? Kudos are appreciated.

[Ole111]

@Kumail 

The business logic should is as follows:

-if he sorting of the customerId is changed by the user in the table:

still first encountered value

-if there are no values after the blank cell in the table:

returns BLANK() - there is no value to refer to.

Best regards,

Dariusz
 

[Kumail]

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