Forum Discussion
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
- MariuszCommunity 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- setisPost Partisan
- MariuszCommunity 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