SUM value from only the first occurrence

[ermak]

I have two tables ('UserID' and 'User_Balance'). In one - the source of traffic and user ids, in others, user ids and their balances. I create a connection and display into a table just the sums of user balances. But, one user can go from several traffic sources and his balance will be indicated everywhere. As a result, the total amount is considered incorrect. Can I create a measure that calculates the balance amount for the first occurrence only? Thanks!

My test 

UserID

Source	UserID	Date
google / cpc	111	01.01.2020
google / organic	222	01.01.2020
google / organic	111	02.01.2020
yahoo / organic	333	03.01.2020
(direct) / (none)	222	03.01.2020

User_Balance

UserID	User_Balance
111	123
222	444
333	323

Expected Result

Source	User_Balance
(direct) / (none)
google / cpc	123
google / organic	444
yahoo / organic	323

Current result

[stevedep · Accepted solution]

Hi @ermak ,

See below:

__New = 
SUMX(SUMMARIZE(ALL_HITS,ALL_HITS[user_id], "b",
IF(
 CALCULATE(FIRSTNONBLANK(ALL_HITS[date],MIN(ALL_HITS[hits_referer])), ALLEXCEPT(ALL_HITS, ALL_HITS[user_id])) = MIN(ALL_HITS[date]) , MIN('Users Balance'[Balance USD]), BLANK())
),[b])

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 

Steve. 

[ermak]

@stevedep Thank you very much! On test data, your solution works for me. But when I try to apply it to a working project it doesn't work. And I can't figure out what's the matter.

Link

[ermak]

@Greg_Deckler, thanks for the advice. I changed the first post a little.

I tried the FIRSTNONBLANK function but it doesn't work.

sum = SUMX(FIRSTNONBLANK (User_ID[Source],'User_ID'[Source]),SUM(User_Balance[User_Balance]))

[Greg_Deckler]

@ermak A little hard to follow. Maybe you want Lookup Min/Max? https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

Otherwise, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.