Ignoring a relationship

[setis]

Dear experts, 

In the following data model

I am trying to calculate some costs in G_L that do not have a Case ID. I need to ignore the relationship between G_L and Cases. 

The G_L table is the following:

As you can see, when I select a year, the rows that don't have a Case ID are logically ignored:

The measure that I'm trying is the following:

Measure = 
CALCULATE (
    SUM(G_L[Amount]);
    FILTER ( G_L; G_L[DEPT] = "610" );
    FILTER ( G_L; G_L[PROJECT] = "FO" );
    FILTER ( G_L; G_L[G_L Account Nr] = "3110" || G_L[G_L Account Nr] = "3130" );
    USERELATIONSHIP ( G_L[Posting date]; 'Date'[Date] )
)

However, as you can see in the 2 screenshots above, I'm getting blank results. 

The desired result is 3600.

I have uploaded this dummy file here: https://drive.google.com/file/d/1XEmbc4Z0DOq_cG3OU3ZaVi4FL8fc97qJ/view?usp=sharing

I would really appreciate some assistance 🙂

[Mariusz · Accepted solution]

Hi @setis 

Try this

Measure = 
CALCULATE (
    SUM(G_L[Amount]),
    G_L[DEPT] = "610",
    G_L[PROJECT] = "FO",
    G_L[G_L Account Nr] IN { "3110", "3130" },
    USERELATIONSHIP ( G_L[Posting date], 'Date'[Date] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[setis]

Hi @Mariusz ,

It's works! WHY? 🙂 

Thank you so much!

[Mariusz]

Hi @setis 

I think its because of the table insight the FILTER( ) is still preserving filter context without USERELATIOSHIP applied,

in your scenario using FILTER was not necessary or beneficial anyway.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn