Forum Discussion

dhersz's avatar
dhersz
Frequent Visitor
8 years ago

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

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

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      dhersz
      Frequent Visitor
      8 years ago

      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