Filter Fact Table on inactive relationship and then calculate measure

[VadGln]

Hello everyone,

I am somehow stuck.

I have a dimension table (let's call it DCC) with cost centres [Cost Center ID], [Cost Center Name]. I also have a fact table (let's call it FP) with [Cost Centre ID Project], [Cost Centre ID Employee], [Project Hours].

The two tables are linked by an active relationship between DCC[Cost Centre ID] and FP[Cost Centre ID Project]. The inactive relationship exists between DCC[Cost Centre ID] and FP[Cost Centre ID Employee]. I would like to calculate how many hours per project have been incurred in a cost centre BU IT (i.e. active connection), but at the same time employees from a cost centre BU GR (i.e. inactive connection) must not be counted.

I have tried this formula:
Project Hours without BU GR =
CALCULATE (
[Project Hours],
FILTER (
ALL ( DCC[Cost Centre Name] ),
NOT ( DCC[Cost Centre Name] ) IN { "BU GR" }
),
USERELATIONSHIP ( DCC[Cost Centre ID], FP[Cost Centre ID Employee] )
)

Unfortunately, the employees from Cost Centre BU GR are filtered out, but at the same time the filter context for [Cost Centre ID Project] (namely Cost Centre BU IT) is ignored.

Does anyone have an idea how to implement this?  I need to filter the fact table via the inactive relationship first and then do the calculation with all filter contexts.

Thanks for support.

[siddharth1k]

see if this code works for you

Project Hours without BU GR =
CALCULATE (
[Project Hours],
// Step 1: Activate the inactive relationship for filtering Cost Centre ID Employee
USERELATIONSHIP ( DCC[Cost Centre ID], FP[Cost Centre ID Employee] ),

// Step 2: Ensure the Cost Centre ID Project context remains intact
KEEPFILTERS ( DCC[Cost Centre Name] = "BU IT" ),

// Step 3: Exclude rows where employees belong to BU GR
FILTER (
ALL ( DCC[Cost Centre Name] ),
NOT ( DCC[Cost Centre Name] = "BU GR" )
)
)

[VadGln]

Thank you for your suggestion.
But as I understand, as long as the USERALATIONSHIP is active within the CALCULATION, the KEEPFILTRES will also use the inactive aka [Cost Center ID Employee] relationship, but for the filtering of BU IT it should be the active aka [Cost Center ID Project] relationship. Also, the filtering part of BU IT should not be coded "hardcopy" in the measure.

[PaulOlding · Accepted solution]

Hi @VadGln 

You may need to do the calculation in 2 parts.

VAR _ExcludeEmployees = 

CALCULATETABLE (
   DISTINCT(DCC[Cost Centre ID]),

   REMOVEFILTERS(DCC),
   DCC[Cost Centre Name] = "BU GR"
)

VAR _Result = 

CALCULATE(

    [Project Hours],

    NOT FP[Cost Centre ID Employee] IN ( _ExcludeEmployees )  

)

RETURN

    _Result

[VadGln]

Thank you for your suggestion. I have tried the solution and it works and I get the result I expected!

I am just wondering, for my understanding, is this solution best practice in terms of performance, or should I have changed my data model to make the calculation via USERALATIONSHIP work?

[PaulOlding]

That's an interesting question.  I don't think I'll be able to give a definitive answer so if anyone else has opinions on it I'd be interested to read them.

My 2 cents: I'd expect a physical relationship to be faster than virtual one https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/ .  To use physical relationships for this calculation you'd need 2 DCC tables, both with an active relationship to the fact.  Most best practice guides I've read don't recommend having these role-playing dimensions, preferring to have a single copy of the dimension table with multiple relationships to the fact (as you have it now).  Personally, I almost always ignore that advice and use role-playing dimensions.  This is mostly because I'm creating models for business users to build reports with, and I find it's less intuitive to understand you need to use a different copy of a measure depending on which relationship you want active, and the same column can have a different meaning depending on which measure you use.  This certainly leads to models being larger in size though.

I guess the ultimate best practice is 'try it out with your model and data'.  Benchmarking the options with your exact situation is going to give you the best information on performance to make a decision.

[VadGln]

Thanks for your 2 cents ;). I will give it a try and try to post the results of the benchmark here.