Forum Discussion

Irwin's avatar
Irwin
Helper IV
4 years ago
Solved

Related + userelationship ?

Hi Guys,

I need to create a calculated column with userelatioship.

So far I have made a monthyear column with related refering to my date table.

 

This works fine and the date is displayed from the "deadline" of the task:

   

I do however have a secondary date. "Start date". I need to create another "monthyear-Startdate"  column.

So far I have tried with this measure... but the column is just empty?

 

MonthStartDate = LOOKUPVALUE(DimCalendar[MonthYearKey], DimCalendar[CalendarKey], 'TaskTable'[Start Date])

 

Why? Any help is appreciated 🙂

 

Need Help
  • Irwin's avatar
    Irwin
    4 years ago

    Okay I managed to solve it with this article:

    https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

     

    Its long and complicated... I didnt understand everything but once I used their measure it worked.

     

    MonthStartDate =
    CALCULATE (
    SELECTEDVALUE ( DimCalendar[MonthYearKey] ),
    CALCULATETABLE(
    'TaskTable(Lots)',
    USERELATIONSHIP ( 'TaskTable(Lots)'[Start Date], 'DimCalendar'[CalendarKey] ),
    REMOVEFILTERS('DimCalendar')
    )
    )
     
    Thanks for your answer amitchandak, I will definately try it out as well.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Irwin , hope Start Date does not have timestamp

    Try a new column like

    MAxx(filter(DimCalendar,DimCalendar[CalendarKey] = datevalues( 'TaskTable'[Start Date]) ) , DimCalendar[MonthYearKey])

    • Irwin's avatar
      Irwin
      Helper IV
      4 years ago

      Hi again,

       

      Unfortunately I could not get your formula to work. It just returns a blank value. Thank you very much for helping 🙂

  • Irwin's avatar
    Irwin
    Helper IV
    4 years ago

    Okay I managed to solve it with this article:

    https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

     

    Its long and complicated... I didnt understand everything but once I used their measure it worked.

     

    MonthStartDate =
    CALCULATE (
    SELECTEDVALUE ( DimCalendar[MonthYearKey] ),
    CALCULATETABLE(
    'TaskTable(Lots)',
    USERELATIONSHIP ( 'TaskTable(Lots)'[Start Date], 'DimCalendar'[CalendarKey] ),
    REMOVEFILTERS('DimCalendar')
    )
    )
     
    Thanks for your answer amitchandak, I will definately try it out as well.