Circular Dependency

DAX DAX Calculations
1

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)

Could somebody please help?

Regards,

2

@rm.gohain,

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

image
image862Ă—267 8.33 KB

No more circular dependency:

image
image636Ă—507 25.8 KB

image

I hope this is helpful.

– Brian

1 Like
3

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

image
image1492Ă—428 55.2 KB

image
image1474Ă—435 56.9 KB

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

image
image1483Ă—446 55.2 KB

image
image1468Ă—426 53.8 KB

5 Likes
4

Well, of course that’s what I meant too – I’m just more concise… :laughing:

Great response, @AntrikshSharma.

  • Brian
3 Likes
5

Hahaha :joy: :grin:

6

@AntrikshSharma,

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
7

@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
8

Thank you all for your help.
Regards,