# Circular Dependency

Hi experts

I am not sure, What I am doing wrong here.

I have created a group table.

Circular Dependency.pbix (291.4 KB)

I have attached a word document explaining the problem. relationship View - Dax Calculation .docx (63.0 KB)

Regards,

Try replacing VALUES with a function that returns a scalar, such as MAX:

No more circular dependency:

â€“ Brian

1 Like

Thatâ€™s because VALUES considers the blank row added by the engine to the one side of the relationship in case of an invalid relationship, this is not to say that your model has a flaw in any way but it is a proactive approach to ensure in case there are invalid relationship in your model meaning Fact table has items that do not belong to the Dimension table then those items will be grouped under a blank row added to Dimension table.

Now when you try to create the relationship the following happens:

1. Dimension table depends on Fact table for the blank row because of invalid relationship (even though it may not happen at the moment in your case but it can happen in the future, thatâ€™s why it is designed keeping that in mind )
2. Fact table depends on Dimension table for the blank row because of the use of VALUES

Thatâ€™s why there is a circular dependency.

Another solution is to use DISTINCT/ALLNOBLANKROW instead of VALUES/ALL

``````Monthly Grouping =
CALCULATE (
DISTINCT ( 'MONTHLY GROUPING'[Group] ),
FILTER (
'MONTHLY GROUPING',
'CURRENT CONTRACTS TFM'[Term in Month] >= 'MONTHLY GROUPING'[Low]
&& 'CURRENT CONTRACTS TFM'[Term in Month] < 'MONTHLY GROUPING'[High]
),
REMOVEFILTERS ( 'CURRENT CONTRACTS TFM' )
)
``````

.

``````Monthly Grouping =
CALCULATE (
DISTINCT ( 'MONTHLY GROUPING'[Group] ),
FILTER (
ALLNOBLANKROW ( 'MONTHLY GROUPING' ),
'CURRENT CONTRACTS TFM'[Term in Month] >= 'MONTHLY GROUPING'[Low]
&& 'CURRENT CONTRACTS TFM'[Term in Month] < 'MONTHLY GROUPING'[High]
),
REMOVEFILTERS ( 'CURRENT CONTRACTS TFM' )
)
``````

The difference between VALUES/ALL and DISTINCT/ALLNOBLANKROW is evident in the Physical query plan:

If you use DISTINCT/ALLNOBLANKROW you get a -BlankRow property in the query plan

If you use VALUES/ALL you get a +BlankRow property in the query plan

5 Likes

Well, of course thatâ€™s what I meant too â€“ Iâ€™m just more conciseâ€¦

Great response, @AntrikshSharma.

• Brian
3 Likes

Hahaha

Okay, potentially dumb question here but given that the categories in the helper table are mutually exclusive and thus will always return a single value for any value of Term in Month, why not just simplify matters and use a basic aggregator like MIN or MAX as the first term in the calculate function. This avoids the circular reference and also would likely perform faster, since that calculation would be done in the storage engine. What am I missing here?

• Brian

@BrianJ No differences, as you have said there will always be single value in the case, so really doesnâ€™t matter what you use.

Regarding calculation in Vertipaq, it wonâ€™t happen since MAX is being executed on a text field, if you do MAX on MONTHLY GROUPINGâ€™[Sort] then it will be done in SE. But the difference is not even visible.

1 Like

Thank you all for your help.
Regards,