locked
Lookup Tables and Userelationship RRS feed

  • Question

  • Does anyone have any suggestions on how to accomplish the following? In the image below, I'm trying to fetch the related values of Start Date and End Date in Projects1 and Projects2. The problem is the relationship between FiscalYears and Projects2 is inactive.

    I tried using the Userelationship but can't get it to work. Anyone have any ideas? Thanks!

    Tuesday, December 23, 2014 4:53 PM

Answers

  • Hi Greg,

    Yes, I created a single fact table. The problem is I need to aggregate information and separate it by fiscal year. All of this feeds into a single fact table.

    I did use Calculate as you suggested, but using Related didn't work. I'll just use the Values function since it works.

    Thanks for your help!

    -Roland

    • Marked as answer by Charlie Liao Monday, December 29, 2014 9:26 AM
    Tuesday, December 23, 2014 6:34 PM

All replies

  • Measure or calculated column? What is the formula you've tried so far? What are the results/errors you get?
    Tuesday, December 23, 2014 5:01 PM
  • I think I found a solution. If anyone has a better solution please let me know. Thanks!

    =CALCULATE(Values(FiscalYears[StartDate]),Filter(FiscalYears,Projects1[Year1FiscalYear] = FiscalYears[FiscalYear]))

    Tuesday, December 23, 2014 5:05 PM
  • Calculated column. I have it working using the Calculate function as I posted.
    Tuesday, December 23, 2014 5:10 PM
  • Have you tried:

    SomeColumn=
    CALCULATE(
        RELATED( FiscalYears[StartDate] )
        , USERELATIONSHIP( Projects2[Year2FiscalYear], FiscalYears[FiscalYear] )
    )

    I will also restate my suggestion from your earlier thread that you implement a single fact table, rather than an additional table for every year of a project.


    • Edited by greggyb Tuesday, December 23, 2014 5:36 PM hyperlink
    Tuesday, December 23, 2014 5:35 PM
  • Hi Greg,

    Yes, I created a single fact table. The problem is I need to aggregate information and separate it by fiscal year. All of this feeds into a single fact table.

    I did use Calculate as you suggested, but using Related didn't work. I'll just use the Values function since it works.

    Thanks for your help!

    -Roland

    • Marked as answer by Charlie Liao Monday, December 29, 2014 9:26 AM
    Tuesday, December 23, 2014 6:34 PM