Forum Discussion
Summarize Table and Sum duplicates ids
- 5 years ago
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.
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.