Relationship circular dependency calculated column

[PVO3]

Hello data friends,

I'm having an issue that I can't fix. I'm trying to create a relationship between a fact and a dim table, but I receive a circular dependency error. Read a lot of post and documentation, but I still can't figure it out.

Example file 

The issue is a calculated column which has to act as a foreign key in the dim table. This column removes duplicate values by using only the ID of the highest Index, so I should be able to create a one-to-many relationship.

It works perfect if I create a DAX reference table (so the calculated column is just a reference).

FK = 
VAR _index = Dim[Index]
VAR _id =
CALCULATE(
    COUNTROWS(Dim),
    FILTER(
        ALLEXCEPT(Dim,Dim[ID]),
        Dim[Index] > _index
    )
)
RETURN

IF( 
    ISBLANK( _id ) && NOT ISBLANK(Dim[ID]), 
    Dim[ID], 
    Dim[ID] & "-" & Dim[Index]
)

But when trying to create a relationship between both tables I get a circular dependency error. Even when removing all other calculated columns. I can't find the depencency. My fact table has no reference whatsoever, the only DAX code I'm using is the code above. 

Hope one of you is able to help me! Thanks a lot.

[Jihwan_Kim · Accepted solution]

Hi,

Please try to create [FK] calculated column by using the below DAX formula.

And then create a relationship.

FK =
VAR _index = Dim[Index]
VAR _id =
    CALCULATE (
        COUNTROWS ( Dim ),
        FILTER ( Dim, Dim[ID] = EARLIER ( Dim[ID] ) && Dim[Index] > _index )
    )
RETURN
    IF (
        ISBLANK ( _id ) && NOT ISBLANK ( Dim[ID] ),
        Dim[ID],
        Dim[ID] & "-" & Dim[Index]
    )

[Jihwan_Kim · Accepted solution]

Hi,

Please try to create [FK] calculated column by using the below DAX formula.

And then create a relationship.

FK =
VAR _index = Dim[Index]
VAR _id =
    CALCULATE (
        COUNTROWS ( Dim ),
        FILTER ( Dim, Dim[ID] = EARLIER ( Dim[ID] ) && Dim[Index] > _index )
    )
RETURN
    IF (
        ISBLANK ( _id ) && NOT ISBLANK ( Dim[ID] ),
        Dim[ID],
        Dim[ID] & "-" & Dim[Index]
    )

[PVO3 · Accepted solution]

@Jihwan_Kim thanks a lot! Such an easy solution. I even had the solution in front of me the entire time. Just should have used the same technique as with the index... Ps. I swapped the your EARLIER for a variable.

VAR _var = dim[id]

FILTER(dim, 

    dim[id] = _var)