Forum Discussion
Summarize Table and Sum duplicates ids
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.
Community ChampionWill 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.
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!
- edhans
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.