Get value from non related table

[draner]

Hi everybody,

I have a new problem with DAX 🙂

I have three table :

affect (idAffect, idProject, idTask, ...)

Project(idProject, ...)

Task(idProject, neededValue)

I have two relations beetween tables:

affect(idProject) <=> project(idProject)

task(idProject) <=> project(idProject)

Now, I want to get a value "neededValue" from task to affect.

But I can't enable an active relation without create ambiguity.

How I can get value "neededValue" in affect ?

best regard

[v-shex-msft · Accepted solution]

Hi @draner,

According to your description, I'd like to suggest you use the lookupvalue function to search for the related value.

RelatedValue=Lookupvalue(task[neededValue],task[IdProject],affect[IdProject])

LOOKUPVALUE Function (DAX)

Regards,

Xiaoxin Sheng

[Datatouille]

Hi,

Could you please be more precise ?

I guess IdTask is also in your Task Table ? (not mentionned in your list).

For both of your active relationships, Project ID is on the "1-side" of the relationship (Dimension tables which each row being unique), right ?

Can the ID project be repeated in Task and/or Affect Table?

And last, what do you want to have in your pivot ?

Depending on your answer you will probably either need:

(i) to use a Bridge Table because of a "Many-To-Many" relationships

(ii) to use "UseRelationship" function which allows you to use an inactive relationship locally in a measure.