locked
Avoiding Circular Dependcy RRS feed

  • Question

  • (The images aren't showing and I'm getting flagged as spam when I try to add them. Ill try to describe the images as best I can)

    Hello,

    I've got two tables one has a column that lists different projects as they come up. The other has a column the gives quantities for different parts that are released for these projects. So I'd like to just create a relationship between the two project ID's in order to create my reports. I think altering the lengths of the project ID's in the second table so they match the first table is the best way to accomplish this.

    The issue is that sometime the Project ID's in the first table are longer than others (See with the PRJ_00760-02-02 etc..) So some PRJ ID's are 16 characters like that but most are the first 10 characters.

    Here in the second table the parts are released in all types of project ID's. These project ID's are either 10, 13, or 16 characters. This causes most of them not to match up with the other tables ID's.

    So in the second table I have a lookup to the project ID's in the first table. Obviously there are a bunch of blanks because the Id's don't all match up. I've used this function

    IF

    (ISBLANK(InstanceCovBIReleaseDate[Calculated Column 1]), LEFT(InstanceCovBIReleaseDate[Project ID], 10), InstanceCovBIReleaseDate[Calculated Column 1])

    (Calculated Column 1 being the lookup to the first table's project ID's). With this function the project ID's are all able to match but when I go to create the relationship between the two tables I get a circular dependency between my two calculated columns.

    Thanks in advance.


    Thursday, March 15, 2018 4:10 PM

All replies

  • Hi jshinnenkamp,

    Thanks for your quesiton.

    According to your description, you might need to share DAX formula you are using for InstanceCovBIReleaseDate[Calculated Column 1] to answer this question.

    It is much better if you can type out 5-10 rows of example data for these tables, then showing what results you are expecting based on those sample data? Do mask sensitive data before uploading.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 16, 2018 5:48 AM
  • Calculated column one is just a lookup to the Project ID's in the first table. This is so I can see what Project ID's match and what ones don't.

    LOOKUPVALUE

    ('Machine Unit Planning1'[Project], 'Machine Unit Planning1'[Project], InstanceCovBIReleaseDate[Project ID]).

    So I'm basically looking for a way to match all of the Project ID's in both tables. When the Project ID's are 16 characters in the first table they match the second, but when they are only 10 characters they don't. So when they don't match I want to shorten the second tables project ID's. Which is what I tried to do with the pasted formula in the original post. Then finally I need to create a relationship between the two tables.

    Friday, March 16, 2018 1:03 PM
  • Hi jshinnenkamp,

    Thanks for your question.

    I can not reproduce this issue as you said. I have created below two sample tables based on your description.


    Creating calculated column SearchColumn in ProjAmount table.
    SearchColumn =
    LEFT ( ProjAmount[ProjID], 4 )


    Creating calculated column LookupValue in Project table.
    LookupValue =
    LOOKUPVALUE ( ProjAmount[ProjID], ProjAmount[ProjID], Project[ProjID] )

    Creating calculated column DesiredColumn in Project table.

    DesiredColumn =
    IF (
        ISBLANK ( Project[LookupValue] ),
        LOOKUPVALUE ( ProjAmount[ProjID], ProjAmount[SearchColumn], Project[ProjID] ),
        Project[LookupValue]
    )


    In the end, create relationship using [DesiredColumn] in table project and [ProjID] in table ProjAmount.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, March 19, 2018 5:45 AM
  • This issue with this is there can be multiple of the same project ID. So I need it to be the opposite. The project table lists each id once but the amount table has them broken down and can list them multiple times. When I try to do it your way I get the multiple values returned error.

    The other factor is id like the desired column in the amount table so I can use it in calculations for the report.

    Monday, March 19, 2018 8:08 PM
  • Hi jshinnenkamp,

    Thanks for your response.

    >>>The project table lists each id once but the amount table has them broken down and can list them multiple times. When I try to do it your way I get the multiple values returned error.
    I have added a record with ProjID 0004FGH to my table to reproduce this issue, but still can not get the error as you said. See below images:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Tuesday, March 27, 2018 5:11 AM
    Tuesday, March 20, 2018 6:51 AM