Forum Discussion

rachaelwalker's avatar
rachaelwalker
Icon for Resolver III rankResolver III
5 years ago
Solved

Conditional Lookup on a related table

I wanting to pull data from one column into a new one based on information from another column in another table. Here is an example of my tables. I want a new Utilized Hours column to populate "Actua...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    rachaelwalker I screwed up, get rid of the first ) in your IF statement and should be good to go.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

rachaelwalker If I understand this correctly, you could add columns like the following:

Utilized Hours Column =
  VAR __WorkTypeID = [WorkTypeID]
  VAR __UtilizationFlag = MAXX(FILTER('WorkType',[WorkTypeID]=__WorkTypeID),[UtilizationFlag])
RETURN
  IF(__UtilizationFlag),[ActualHours],0)


Non-Utilized Hours Column =
  VAR __WorkTypeID = [WorkTypeID]
  VAR __UtilizationFlag = MAXX(FILTER('WorkType',[WorkTypeID]=__WorkTypeID),[UtilizationFlag])
RETURN
  IF(__UtilizationFlag),0,[ActualHours])
rachaelwalker's avatar
rachaelwalker
Icon for Resolver III rankResolver III
5 years ago

I was thinking I could do LOOKUPVALUE and pull UtilizationFlag (true or false) into the TimeEntries table. Then did an If statement but the numbers are not reflecting accurately. 

Lookup formula

Utilization = LOOKUPVALUE(WorkTypes[WorkTypes.utilizationFlag], WorkTypes[WorkTypes.id], TimeEntries[TimeEntries.workType.id])

If statement

Utilized Hours = IF('TimeEntries'[Utilization] = "True", TimeEntries[TimeEntries.actualHours], 0)