Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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
This worked perfectly! Thank you!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |