Forum Discussion

setis's avatar
setis
Post Partisan
6 years ago
Solved

Ignoring a relationship

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 🙂

 

  • 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

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion

    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's avatar
      setis
      Post Partisan

      Hi Mariusz ,

       

      It's works! WHY? 🙂 

       

      Thank you so much!

      • Mariusz's avatar
        Mariusz
        Community Champion

        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