cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dhersz Frequent Visitor
Frequent Visitor

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

2 REPLIES 2
Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

dhersz Frequent Visitor
Frequent Visitor

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

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.

image.png

 

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 152 members 1,788 guests
Please welcome our newest community members: