Removing Duplicate Rows in Some Situations

[LimitedWisdom]

I'm a complete BI newbie. I've got a table imported. I'm doing some basic analytics that are proving to be more complicated than I thought! 

I have a table of people. I have a handful of columns that contain demographics about those people. In most cases, I need that data sorted by person. I'm running into a few situations where I need the data grouped by household instead.

For example, if I want a sum of all the children, any case where there is a mother and a father in the database will result in the child count being duplicated.  There's a few other columns like this, but if I can just understand this one example, I think I can extrapolate out the rest. Here's some example data:

Person 1      Address 1     Kids     2

Person 2      Address 1     Kids     2

Person 3      Address 2     Kids     1

Person 4      Address 3     Kids     0

Person 5      Address 4     Kids     5

Person 6      Address 4     Kids     5

In this case, the TOTAL number of kiddos is only 8 (as Persons 1 & 2 have the same children, and persons 5 & 6 have the same children). However, I can't figure out how to not get a total of 15.

It's also important that I don't lose the original rows...as some of the other columns have data that needs to be aggregated by individual person and NOT by family.

I hope I'm making sense. I have a feeling that I'm just missing some very basic concept. Any help would be greatly appreciated!

[Ashish_Mathur · Accepted solution]

Hi,

This DAX formula works

=SUMX(SUMMARIZE(VALUES(Table1[Address]),[Address],"ABCD",MIN(Table1[Count])),[ABCD])

Hope this helps.

[LimitedWisdom]

I think this is working...but what's the ABCD? 

Would you be willing to walk through what all of the syntax is doing? I don't think I fully understand what each of those DAX keywords is doing...

[Ashish_Mathur]

Hi,

The SUMMARIZE() function creates a virtual table.  The first input into this function is a Table - the VALUES() function returns a table of n rows and 1 column with all unique addresss.  So with yoru dataset, it will return a Table of 4 rows and 1 column.  Since there is only column in this Table, we can goup only by that one column.  ABCD is the title of another column which we are creating in this virtual table.  Within double quotes you can type any other meaningful name.  Now in each row of this virtual table, under the ABCD column, we are computing the minimum value that appears in the Count column.  The SUMX() function takes two inputs - Table and measure.  The Table is the two column table returned by the SUMMARIZE() function.  The measure is ABCD.

Hope this helps.

[LimitedWisdom]

Thanks so much @Ashish_Mathur - by leveraging this bit of info, I made some great calculations elsewhere and the report looks great! I checked it with some SQL queries and confirmed everything. Really appreciate the quick replies and taking some extra time to explain the logic!

[Ashish_Mathur]

You are welcome.

[edhans]

One way is to use the GROUPBY function. I named your columns Individual, Address, Category, and Number, in that order. This will create a new table that gets rid of the duplicates. In the Modeling Tab, select New Table, then use this formula.

By Category = 
GROUPBY(
    People,
    People[Category],
    People[Address],
    People[Number]
)