Forum Discussion

bmurf's avatar
bmurf
Frequent Visitor
5 years ago
Solved

Summarize Table and Sum duplicates ids

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) ...
  • edhans's avatar
    edhans
    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.

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

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. 

bmurf's avatar
bmurf
Frequent Visitor
5 years ago

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's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      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.

      • bmurf's avatar
        bmurf
        Frequent Visitor
        5 years ago

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