Matrix with real and target values - target measure doesn't appear!

[Anonymous]

Hello, 

i have an error related on how to place [real] Resources and Target values on a matrix. 

The Resources part is OK, the problem starts on a Target dax measure. 

The error is dax and model related, so i'm also not sure where to place the problem. 

So i have a factual Resources with 2 dimensions. The only thing noticeable is that the client has the information on dimensions "one level down" of their name, using the labels to aggregate (cluster job to agg. job codes, and organizationalcodelevel to agg. Perimeters).

So, the problem appears when I create a “target internal” measure from the Target dimension, I can’t see any value, though measure seems very simple:

Target Internal = CALCULATE( MAX('Target'[Target]) )

I’ve done a 1st experience, I think it was relating Cluster Jobs To Target in a n:n relationship, and the same for the other dimension, but it didn’t work.

Then I created List of Cluster Jobs and list of Perimeters, with their distinct values, and set the relationship to their “original” dimensions as bidirectionals, to see if it would flow (and yes, in this case, I had to place these 2 tables in matrix setup…) – didn’t work also.

Then I was trying a 3rd way: forcing the existing filters of the matrix in the measure, didn’t work also:

Target Internal =

CALCULATE( MAX('Target'[Target]), 'Target'[Type] = "Internal",

Target[Perimeter] = ALLSELECTED(Perimeters[Perimeter]),

Target[Cluster Job] = ALLSELECTED('Cluster jobs'[Cluster job]) )

So by doubt is: what is the correct way of making this work?

Thanks in advance

[Anonymous]

Hello @lbendlin , thanks but i still cant make it work

Target Internal = 
CALCULATE( 
  MAX(Target[Target]), 'Target'[Type] = "Internal", 
  TREATAS(VALUES('Perimeters'[Perimeter]), Target[Perimeter]), 
  TREATAS(VALUES('Cluster jobs'[Cluster job]), Target[Cluster Job])
)

As i've written, Perimeters[Perimeter] and Cluster Jobs[Cluster job] is repeated, but this shouldn't be the problem

as VALUES returns distincts..

I've also tried, without success

CALCULATE( MAX(Target[Target]), 'Target'[Type] = "Internal", TREATAS(SELECTEDVALUE('Perimeters'[Perimeter]), Target[Perimeter]), TREATAS(SELECTEDVALUE('Cluster jobs'[Cluster job]), Target[Cluster Job]) )

Any idea?

TIA 

[lbendlin]

I would think that the "correct"  way of doing this is to use TREATAS() instead of trying to force it through a data model.