Multiple combinations of duplicate columns

[fred31330]

Hello,

I have a table like this:

City	Fiscal Period	User	Value	Col5	Col6
A	01-17	User1	100	...	...
A	02-17	User1	120	...	...
A	01-17	User1	100	...	...
B	03-17	User1	200	...	...
A	02-17	User3	100	...	...
B	04-17	User2	300	...	...

 And I would like to determine which rows are showing the following combinations:

• same city, same period, same user, same value
• same city, same period, different user, same value
• same city, different period, same user, same value

Is it possible to achieve this with DAX, or shall I use the PowerQuery editor only?

Thanks!

Fred

[joshcomputer1]

Just looking at what you are requesting...I would suggest to use the query editor. It seems like you want to "group by" city, same period, same user, same value. To do this, go to edit queries. Click Group By. Advanced. Include the different groupings in the top section. The bottom section is probably just a count rows of those groupings (if you have a sum or other aggregate, try that instead of count rows). 

[fred31330]

I appreciate your swift response!

But how can I identify cases such as "same city, different period, same user, same value"?

Say I have the following scenario:

city A, period 1, user B, value C

city A, period 1, user B, value C

city A, period 2, user B, value C

At the end, I would like to have this in my resulting table:

city A, period 1, user B, value C, count 2

city A, period 2, user B, value C, count 1

I understand I'd have to group by city, user and value, but how can I ensure I won't have cases with the same period appearing more than once?

Thanks.

[joshcomputer1 · Accepted solution]

The Group By will look for a grouping of all 4 fields.  So it's like if CIty A, Period1, User A, Value 1 then count +1.

If the user is different, then that would be a different group. 

Here is my inital mock up dataset. 

I did the group by like this:

The result dataset is like this:

[fred31330]

Thank you!