Get the max value of a dimension for each element of a second dimension

[Anonymous]

Hello,

I have a data model that consists of three tables, one fact table, and two dimensions. The dimensions are both joined to the Fact with a one-to-many relationship

I have been asked to find the max date from one dimension (Dim A) for each element in my second dimension (Dim B). I think I need to use Calculate here but I'm not sure how.

Image of a data model

Sample data

Goal results

[lbendlin]

Your indicated expected results cannot be achieved.  Both dimensions (Category and Date) will be aggregated if combined with facts, so you cannot have two rows of B and only one row of A.

What you will need to do is create a measure that crawls through the data model

MaxDate = 
var c = ADDCOLUMNS(VALUES(_FactValues[FactKeyA]),"FA",_FactValues[FactKeyA])
var d = ADDCOLUMNS(c,"MD",CALCULATE(max(_DimDate[Date]),Filter(_DimDate,_DimDate[FactKeyA]=[FA])))
return MAXX(d,[MD])

See attached for an implementation

Another alternative is to show the raw data, the dimensions without the facts.

See attached.