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.
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!!!
- edhans
Please don't delete your response again bmurf - I just lost huge amout of typing.
- edhans
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.
