Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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
Solved! Go to 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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
@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]))
@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.
User | Count |
---|---|
53 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
80 | |
57 | |
40 | |
19 | |
10 |