Forum Discussion

dcor12's avatar
dcor12
Frequent Visitor
3 years ago
Solved

Calculated column pulling value from other table based on earliest date

See screenshots of sample data model and tables. The C_VALUE column is the calculated column I am attempting to create with expected results shown.

 

C_VALUE should populate with the A_VALUE with the the earliest A_STARTDATE. If there are multiple entries with the same start date (See lines A8 and A9), then it would return the lesser of the two values from A_VALUE.

I've been attempting DAX such as:
C_VALUE = LOOKUPVALUE('TABLE A'[A_VALUE], 'TABLE A'[A_STARTDATE], MIN('TABLE A'[A_STARTDATE]))

but it's just giving the same answer for every cell.

Appreciate the help.


TABLE A   TABLE B   TABLE C 
A_IDA_STARTDATEA_VALUE B_IDA_IDC_ID C_IDC_VALUE
A11/1/20238 B1A1C1 C18
A21/2/20235 B2A2C1 C27
A31/3/20244 B3A3C1 C34
A41/4/20247 B4A4C1   
A51/5/20242 B5A4C2   
A61/6/20243 B6A5C2   
A71/7/20246 B7A6C2   
A81/8/20244 B8A7C2   
A91/8/20245 B9A8C3   
A101/9/20244 B10A9C3   
    B11A10C3   
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi dcor12 ,

    You can create a calculated column as below in the table 'TABLE C' to get it, please find the details in the attachment.

    C_VALUE = 
VAR _aids =
    CALCULATETABLE (
        VALUES ( 'TABLE B'[A_ID] ),
        FILTER ( 'TABLE B', 'TABLE B'[C_ID] = 'TABLE C'[C_ID] )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'TABLE A'[A_STARTDATE] ),
        FILTER ( 'TABLE A', 'TABLE A'[A_ID] IN _aids )
    )
VAR _minvalue =
    CALCULATE (
        MIN ( 'TABLE A'[A_VALUE] ),
        FILTER (
            'TABLE A',
            'TABLE A'[A_ID]
                IN _aids
                && 'TABLE A'[A_STARTDATE] = _mindate
        )
    )
RETURN
    _minvalue

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi dcor12 ,

    You can create a calculated column as below in the table 'TABLE C' to get it, please find the details in the attachment.

    C_VALUE = 
VAR _aids =
    CALCULATETABLE (
        VALUES ( 'TABLE B'[A_ID] ),
        FILTER ( 'TABLE B', 'TABLE B'[C_ID] = 'TABLE C'[C_ID] )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'TABLE A'[A_STARTDATE] ),
        FILTER ( 'TABLE A', 'TABLE A'[A_ID] IN _aids )
    )
VAR _minvalue =
    CALCULATE (
        MIN ( 'TABLE A'[A_VALUE] ),
        FILTER (
            'TABLE A',
            'TABLE A'[A_ID]
                IN _aids
                && 'TABLE A'[A_STARTDATE] = _mindate
        )
    )
RETURN
    _minvalue

    Best Regards

    • dcor12's avatar
      dcor12
      Frequent Visitor
      3 years ago

      Thank you, this works beautifully!