topic Summarize Table and Sum duplicates ids in DAX Commands and Tips

Summarize Table and Sum duplicates ids

bmurf — Tue, 27 Apr 2021 21:27:10 GMT

I have two tables "Donor" and "Giving" (see below) joined by [user_id] that are used to generate a 3rd table for export.

The Requirements:
1) Calculate total [amount] given by [family_id]

2) Grab both [first_name] fields when 2 donors share the same [family_id]
* Note below where [family_id] = 207 because of a child; [marital_status] = "Married" identifies which names to pull

Sample Output: (from tables below)

family_id	total	donor1	donor2
200	250	Barrett
204	100	Roger	Clementine
207	425	Freddy	Maggie
208	135	Piper

Thanks for the help! Please see below for sample tables.

Bryan

****************************************************************

Donor Table:

user_id	family_id	first_name	last_name	Marital Status
1	200	Barrett	Aurelia	Single
2	201	Rhiannon	Bailey	Divorced
3	202	Quyn	Aurelia	Single
4	203	Herman	Castillo	Married
4	204	Roger	Hogan	Married
5	204	Clementine	Hogan	Married
6	205	Mendez	August	Divorced
7	206	Keefe	Blake	Single
8	207	Freddy	Cruz	Married
9	207	Maggie	Cruz	Married
10	207	Johnny	Cruz
11	208	Piper	Serrano	Single

Giving Table:

id	user_id	date	amount
7000	6	5-Jan	25
7001	8	5-Jan	60
7002	3	5-Jan	116
7003	4	5-Jan	40
7004	10	8-Feb	30
7005	8	8-Feb	200
7006	7	8-Feb	21
7007	10	8-Feb	70
7008	9	8-Feb	40
7009	2	3-Mar	36
7010	1	3-Mar	200
7011	6	3-Mar	50
7012	4	3-Mar	60
7013	10	3-Mar	35
7014	8	16-Apr	100
7015	1	16-Apr	50
7016	9	16-Apr	25
7017	2	16-Apr	39
7018	3	16-Apr	120

Re: Summarize Table and Sum duplicates ids

edhans — Tue, 27 Apr 2021 22:27:37 GMT

Will this work bmurf

Rather than putting the two donors in seperate columns, I used CONCATENATEX to create a list of up to two people. It doesn't match your output exactly because our DONOR table had a duplicate #4 record, so I whacked it because something has to be unique, and I assumed that was the user_ID.

Donors = VAR varValidDonors = CALCULATETABLE( 'Donor Table', 'Donor Table'[Marital Status] <> BLANK() ) VAR varDonorList = CONCATENATEX( varValidDonors, 'Donor Table'[first_name ], ", ", 'Donor Table'[first_name ], ASC ) RETURN IF( ISINSCOPE('Donor Table'[family_id ]) && [Total Giving] <> BLANK(), varDonorList, BLANK() )

My PBIX file is here if you want to see the whole thing.

Re: Summarize Table and Sum duplicates ids

bmurf — Wed, 28 Apr 2021 00:09:20 GMT

Thanks! I'm pretty sure it's what I need - just trying to follow the logic of the code (particularly isinscope!).
Doing a little research, but THANKS for the fast reply! I'll accept the solution shortly!

Re: Summarize Table and Sum duplicates ids

edhans — Wed, 28 Apr 2021 00:31:06 GMT

INSCOPE just prevents the measure from reporting names on the TOTAL row. The Family ID field is not "in scope" on the total row, so it will return blank for you if your visual has a total row. If it doesn't this doesn't do anything and isn't necessary.

Re: Summarize Table and Sum duplicates ids

bmurf — Wed, 28 Apr 2021 19:31:49 GMT

edhans,

I worked thru it - concatnatex is cool, never used it before!

2 issues:
1) I looked the PBIX file and couldn't figure out how you made [family_id] = 204 go away???
I changed Roger Hogan's [user_id] to "12" and I thought 204 would show up in the output after refresh, but it didn't?

2) Sample Data error:
the [marital_status] for [user_id]=10 should be "single." The idea is that 3 or more people could have the same [family_id] because of kids, but only 2 will have "Married" so I know which two names to concatenate. So I think varValidDonors needs to filter on [marital_status] = "Married."
I tried to make two VARs: 1 for "Married" and 1 for "Single" and use UNION to combine them, but my syntax was wrong. Besides, you'll probably have a better method...

If you can tweak that 2nd issue, I'm all good!

Thanks again!!!

Re: Summarize Table and Sum duplicates ids

edhans — Wed, 28 Apr 2021 19:46:22 GMT

Please don't delete your response again bmurf - I just lost huge amout of typing.

Re: Summarize Table and Sum duplicates ids

edhans — Wed, 28 Apr 2021 19:49:30 GMT

I'm not typing all of my stuff again. in summary, i changed roger to 12, but there is no donor data in 12 so nothign shows up.

Donors = VAR varCurrentFamily = SUMMARIZE( 'Donor Table', 'Donor Table'[family_id ], 'Donor Table'[user_id ], 'Donor Table'[Marital Status] ) VAR varMemberCount = COUNTROWS( varCurrentFamily ) VAR varValidDonors = IF( varMemberCount > 2, CONCATENATEX( CALCULATETABLE( 'Donor Table', 'Donor Table'[Marital Status] = "Married" ), [first_name ], ", ", [first_name ], ASC ), CONCATENATEX( 'Donor Table', 'Donor Table'[first_name ], ", ", 'Donor Table'[first_name ], ASC ) ) RETURN IF( ISINSCOPE( 'Donor Table'[family_id ] ) && [Total Giving] <> BLANK(), varValidDonors, BLANK() )

This measure will look for families with more than 2. If it finds that, it only pulls married, otherwise it does the normal stuff. the PBIX file I linked to above can be used again. It has this new code. There is probably a more efficient way to do this, but this works.

Re: Summarize Table and Sum duplicates ids

bmurf — Wed, 28 Apr 2021 20:44:57 GMT

Awesome, Thanks!!!
Sorry about the delete!😞

Re: Summarize Table and Sum duplicates ids

edhans — Wed, 28 Apr 2021 21:06:37 GMT

Great bmurf - glad I was able to assist here!