Unable to create measure or column using calculated column

[OPS-MLTSD]

Hello,

I created a calculated column in PBI Desktop and this is the dax I used to create the column: 

Catchment2 = LOOKUPVALUE('user_catch-2'[EST_CATCHMENT_AREA], 'user_catch-2'[CREATED_BY], 'CA usage'[CREATED_BY])

Now that I have this new column, I have some blank values in my Catchment2 column in the CA usage table, which I would like to replace with "Unassigned", and so, I tried to create another table in the 'CA usage' table using this formula:

New Catchment = IF(ISBLANK('CA usage'[Catchment2]), "Unassigned", 'CA usage'[Catchment2])

however I get an error message saying : A circular dependency was detected: CA usage[Catchment2], CA usage[Column], CA usage[Catchment2].

I tried to create a new measure to solve this issue but it did not work, I was wondering if anyone could please help me create a new column in my 'CA usage' table that contains the same value as the Catchment2 column but instead, the blanks are replaced with "Unassigned".

thank you

[Payeras_BI · Accepted solution]

Hi @OPS-MLTSD ,

Try with this:

Catchment2 = LOOKUPVALUE('user_catch-2'[EST_CATCHMENT_AREA], 'user_catch-2'[CREATED_BY], 'CA usage'[CREATED_BY], "UNASSIGNED")

[AlB · Accepted solution]

Hi @OPS-MLTSD 

You can do it on the first column directly, no need to create a new one:

Catchment2 =
VAR aux_ =
    LOOKUPVALUE (
        'user_catch-2'[EST_CATCHMENT_AREA],
        'user_catch-2'[CREATED_BY], 'CA usage'[CREATED_BY]
    )
RETURN
    IF ( ISBLANK ( aux_ ), "Unassigned", aux_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[OPS-MLTSD]

you're the best, thank you!

[Payeras_BI · Accepted solution]

Hi @OPS-MLTSD ,

Try with this:

Catchment2 = LOOKUPVALUE('user_catch-2'[EST_CATCHMENT_AREA], 'user_catch-2'[CREATED_BY], 'CA usage'[CREATED_BY], "UNASSIGNED")

[OPS-MLTSD]

thank you so much!!