Modeling issue - TREATAS & Inactive relationships

[CarlsBerg999]

Hi,

I have a data modeling issue that i can't seem to resolve. The data model is described below. What I need to do, is use the report filter to filter the results in the Matrix table.

The issue is rather simple: The filter is not working for attributes because there is no active relationship. I cannot activate a connection between Filter Table and Attributes table, because it would cause ambiguity. The usual resolution would be USERELATIONSHIP, but it doesnt seem to resolve the issue in my measure.

Twist: I need to use "TREATAS" in the table due to complexity of the entire model. 

The current measure i have is: 

CALCULATE(SUM('Fact Table'[Value]),

TREATAS(VALUES('Categories'[Categories]),'Fact Table'[Categories]),

'Fact Table'[Product ID]IN{"20002"})

--> This does not produce results in the matrix table. It does work, if i switch on the relationship from the attributes to filter.

Any ideas on how to make this work? Note, i absolutely must use TREATAS for the categorization.  

[Anonymous · Accepted solution]

@CarlsBerg999 

Add a relationship from Categories Table to the Fact Table. 

And try use the following measure with TREATAS ():

Measure = CALCULATE(SUM('Fact Table'[Value]),

TREATAS(VALUES('Filter Table'[Filter]),'Attributes'[Filter]),

'Fact Table'[Product ID]IN{"20002"})

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.