Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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_ID | A_STARTDATE | A_VALUE | B_ID | A_ID | C_ID | C_ID | C_VALUE | ||
A1 | 1/1/2023 | 8 | B1 | A1 | C1 | C1 | 8 | ||
A2 | 1/2/2023 | 5 | B2 | A2 | C1 | C2 | 7 | ||
A3 | 1/3/2024 | 4 | B3 | A3 | C1 | C3 | 4 | ||
A4 | 1/4/2024 | 7 | B4 | A4 | C1 | ||||
A5 | 1/5/2024 | 2 | B5 | A4 | C2 | ||||
A6 | 1/6/2024 | 3 | B6 | A5 | C2 | ||||
A7 | 1/7/2024 | 6 | B7 | A6 | C2 | ||||
A8 | 1/8/2024 | 4 | B8 | A7 | C2 | ||||
A9 | 1/8/2024 | 5 | B9 | A8 | C3 | ||||
A10 | 1/9/2024 | 4 | B10 | A9 | C3 | ||||
B11 | A10 | C3 |
Solved! Go to Solution.
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
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
Thank you, this works beautifully!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |