Create a relationship between column created via LOOKUPVALUE and the source column

[dhersz]

Hello,

I'm trying to create a relationship between two columns, but one of them was created via LOOKUPVALUE and the column from which it gets the value is exactly the one I'm trying to relate to. Is that possible?

What I'm trying to achieve is the following:
I have a database of clients' open projects and a database of yearly goals, with clients in it. Ideally I'd like to create a relationship between this columns, but that would result in an error, since it would be a multiple values to multiple values relation.

My solution was to create an intermediate table between these, with unique names of clients.

However, some of the clients with open projects were not predicted in the yearly goals (and I need the visual to be based on the goals' sheet), so I created a field called "other" as well. So in my open projects sheet I created a calculated column with LOOKUPVALUE (so it returns either the name of the client or "other").

Now I'd like to create a relationship between this new column and the intermediate table (so I can use the intermediate table's "client" field to filter my visuals) but it results in a "circular dependency" (not sure if that's the term, my power bi language is portuguese).

Is there a better way to make what I'm trying to achieve?

Thank you in advance

[Greg_Deckler]

Yeah, that would definitely be a circular dependency and DAX is seriously not OK with that. If you could provide some sample/example data, this would be far easier to understand and see if there is a better way.

[dhersz]

No worries, I'll provide some pictures and show what I've been doing already.

So here is how I'm doing right now (with another table, "Vertical").

Both the "CRM" and "Meta" tables also have "Vertical" fields, and I created this relationship below so I can apply one filter to alter visuals based on both tables.

I'm trying to do the same with the "Clients" field on both table. However, these columns differ a bit from each other.

Let's say that in the CRM table I have something like this:

Stage | Value | Client
Stg 1 | 1000  | Client A

Stg 2 | 2000  | Client B

Stg 1 | 1000 | Client C

And in the "meta" table I have yearly goals for both Client A and Client B, but no C, so I'd like to create another column which would return me something like:

GoalsClient

Client A

Client B

Other

Now, I did this with LOOKUPVALUES, just looking for the client in the column and, in case of blank, fill it with "Other".

Then I would like it to a Unique Values table, just like I did with "Vertical" and I'd create a slicer to filter my visuals.

The code right now to the LOOKUPVALUES looks like this:

ClienteMeta = IF(
    ISBLANK(LOOKUPVALUE('Info cliente'[teste], 'Info cliente'[Cliente], 'CRM - Acompanhamento geral'[Cliente])) = FALSE(),
    LOOKUPVALUE('Info cliente'[teste], 'Info cliente'[Cliente], 'CRM - Acompanhamento geral'[Cliente]),
    "Outros/Pulverizado"
)

(The LOOKUPVALUES is looking up to the Unique Values table, not the "Meta[Client]" column (it returns circular dependency anyway))

Is it clearer now?

Thank you very much