

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count of Mode of 3 out of 4 dimensions
I have 4 Dimensions in a table [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] and [TDW Amz Reg] (the field [Full Legal Entity Name] can be ignore as it's the long name of [TDW Legal Entity Code]). See screenshot.
I am trying to create some Measures about [TDW Amz Reg] based on the following 3 dimensions [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]. For example the (most frequent) Mode [TDW Amz Reg] for [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] and the Count of the Distinct [TDW Amz Reg] by [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]. i.e.
=aggr( Mode([TDW Amz Reg]), [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction])
Count(Distinct TOTAL <[Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]> [TDW Amz Reg])
So far so good. Now I want to count of how frequently the Mode [TDW Amz Reg] per [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] appears. I'm struggling because I don't think Aggr will work with having the [TDW Amz Reg] as a column.
