locked
Any idea on how to perform a lookup on a Related Table RRS feed

  • Question

  • Here is the data:

    ProjectId Stage StateStatus ProjectId ProjectName StartDate
    P1 S1 Completed P1 P1Name 31/12/2015
    P1 S2 In Progress P2 P2Name 25/12/2015
    P1 S3 Not Started P3 P3Name 31/01/2016
    P1 S4 Not Started
    P2 S1 Completed
    P2 S2 Completed
    P2 S3 In Progress
    P2 S4 Not Started
    P3 S1 Completed
    P3 S2 Completed
    P3 S3 Completed
    P3 S4 Completed

    I want with PowerPivot to tell what stage is in progress for project. I looked at RElatedTable, LOOKUPVALUES, but I can't find a way to associate this to get working.

    I got however the one telling me which projects are Completed.

    Result shall be:

    ProjectName StageinProgess

    P1Name S2

    P2Name S3

    P3Name None

    Any Idea would be appreciated.

    Tuesday, May 12, 2015 3:27 PM

Answers

  • Yes. That is working. Thanks a lot.

    That sounds obvious when you read it. But I spent hours yesterday locked on the RelatedTable and Lookupvalue, trying to find the way to connect those 2.

    But using only LOOKUPVALUES and the IF trick is doing the job. The

    Thanks again.

    Wednesday, May 13, 2015 8:50 AM

All replies

  • Hi Michael,

    Within your PowerPivot model which to diagram view.

    Drag a relationship between the Blue Project->P1 and the Black Project->P1 columns.

    Within the Blue Project table you need to create a calculated column which contains; -

      RELATED DAX function, i.e. RELATED (Black_Project_Table[Stage]).

    Create a further calculated column which contains; -

      RELATED DAX function, i.e. RELATED (Black_Project_Table[StateStatus]).

    Kind Regards,

    Kieran. 


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/



    Tuesday, May 12, 2015 7:57 PM
  • Hi Michael,

    According to your description, you need to get the "In Process" Stage for each Project ID, right?

    I have tested it on my local environment, the DAX expression below is for you reference.
    =IF(lookupvalue(case0513[Stage],case0513[ProjectID],[ProjectID],case0513[StateStatus],"In Process")="","None",lookupvalue(case0513[Stage],case0513[ProjectID],[ProjectID],case0513[StateStatus],"In Process"))

    Reference
    https://msdn.microsoft.com/en-us/library/gg492170.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, May 13, 2015 8:25 AM
  • Hi Kieran,

    I got the relationship in place already.

    And RELATED does not work.

    Wednesday, May 13, 2015 8:41 AM
  • Yes. That is working. Thanks a lot.

    That sounds obvious when you read it. But I spent hours yesterday locked on the RelatedTable and Lookupvalue, trying to find the way to connect those 2.

    But using only LOOKUPVALUES and the IF trick is doing the job. The

    Thanks again.

    Wednesday, May 13, 2015 8:50 AM