Find Duplicate Count of related Table

[HansTheEnforcer]

Here's the situation,

I have two tables with a 1 to Many relationship: Location and Asset.  I'm looking to find and flag TRUE or FALSE locations that have multiple Assets of the same Size and of the same Material.  See Below for table examples.  

Asset Table

Asset ID	Location Number	Size	Material
2234	1234	1	Wood
2235	1234	1	Wood
2236	1234	2	Wood
2237	1235	4	Carpet
2238	1236	1	Carpet
2239	1236	1	Wood
2240	1236	3	Carpet
2241	1237	1	Wood

Location Table

Location Number	Related to Multiple Assets of the same Type
1234	TRUE
1235	FALSE
1236	FALSE
1237	FALSE

[OwenAuger · Accepted solution]

Hi @HansTheEnforcer 

I would propose creating a calculated column like this in Location:

Related to Multiple Assets of the same Type =
CALCULATE (
    MAXX (
        SUMMARIZE ( Asset, Asset[Material], Asset[Size] ),
        CALCULATE ( COUNTROWS ( Asset ) )
    )
) > 1

[HansTheEnforcer]

This worked perfectly!  Thank you!

[VasTg]

@HansTheEnforcer 

Try this as a calculated column in Location Table.

Column =
VAR A =
    RELATEDTABLE ( 'Asset Table' )
VAR SIZE_CNT =
    CALCULATE ( DISTINCTCOUNT ( 'Asset Table'[Size] ), A )
VAR MATERIAL_CNT =
    CALCULATE ( DISTINCTCOUNT ( 'Asset Table'[Material] ), A )
RETURN
    IF ( SIZE_CNT > 1, FALSE (), IF ( MATERIAL_CNT > 1, FALSE (), TRUE () ) )

If this helps, mark it as a solution.

Kudos are nice too