Issue with Data Model

[AnSc93]

Hi together,

I have the following data model (reduced view):

I need to have the relationship between DimCostCenter and FactAllValues active but it is not possible due to the both filter direction of DimResource and DimCostCenter with the table FactCCLocationOrg.

The main reason why I have the filter direction both for the table DimCostCenter is that I need to filter the FactBudget with the DimManager.

Is there any other way to set up the current model to get the relationship active between table DimCostCenter and FactAllValues?
Maybe by setting up the relationship with DimManager in another way to avoid the filter direction in both ways with DimCostCenter?

I'm really stuck here and already tried a few things. Could somebody help me out?

Thanks in advance.

Best regards,

Andreas

[AnSc93]

Hi @Anonymous ,

thank you for your feedback an explanation.

I will try your provided proposal regarding the DAX functions.

Best regards,

Andreas

[Anonymous · Accepted solution]

Hi @AnSc93,

The power bi data model is based on AS tableau engine and it only supports one active major relationship. Multiple relationships will cause ambiguity and affect the recognition and aggregation of different table records based on the relationships.

Relationships in analysis services tabular models 

I think you can try to write measure formulas to calculate with TREATAS or USERELATIONSHIP functions.

TREATAS function - DAX | Microsoft Docs

USERELATIONSHIP function (DAX) - DAX | Microsoft Docs

Power BI USERELATIONSHIP vs TREATAS - Enterprise DNA

Regards,

Xiaoxin Sheng