none
Show One value in all the rows in a single column RRS feed

  • Question

  • Hi 

    I have a data in following format in my tabular model

    C_ID T_Date Capacity
    1 8/2/2019 10
    1 8/3/2019 20
    1 8/4/2019 30
    1 8/5/2019 40
    1 8/6/2019 50

    Need to create two derived columns using DAX,which will show data in the below format

    C_ID T_Date Capacity Least Date Least Capacity
    1 8/2/2019 10 8/2/2019 10
    1 8/3/2019 20 8/2/2019 10
    1 8/4/2019 30 8/2/2019 10
    1 8/5/2019 40 8/2/2019 10
    1 8/6/2019 50 8/2/2019 10

    Thanks in advance..

    Wednesday, August 14, 2019 10:25 PM

All replies

  • Hi AlwaysLearner08.

    Here is DAX expression for the two derived columns.

    [Least Date]=CALCULATE(MIN(CTC[T_Date]),ALL(CTC))
    
    [Least Capacity]=CALCULATE(min(CTC[Capacity]),ALL(CTC))

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 15, 2019 2:36 AM
  • Hi Will,

    Thanks for the suggestion.

    The least capacity column should show the value of the minimum date i.e., if the value for the date in least date column is 100, it should show 100 in all the rows in for that column.


    Thursday, August 15, 2019 4:42 PM
  • As a general rule of thumb you should always avoid using calculate() in calculated columns as it can lead to circular dependency errors.

    And in this case it's not needed, you can simply do:

    Least Date = MIN('Table'[T_Date])

    Least Capacity = LOOKUPVALUE('Table'[Capacity],'Table'[T_Date], 'Table'[MinDate])


    http://darren.gosbell.com - please mark correct answers

    Friday, August 16, 2019 4:04 AM
    Moderator