Write logic for Calculated Column or Measure

[manojk_pbi]

Hi,

I have 2 tables recording the details for lessonsLearned in the process and another one is record lessonlearned related activity.

I need to create a calculated column in lessonlearned table or a new table with all necessary details to use it for reporting. 

Any suggestions are well appreciated. Thanks in advance

LessonLearned table contains 

LLID is unique key between both the tables.

LLID	Milestone	StatusUpdated
L1	M1	02-Dec-2025
L2	M2	02-Dec-2025
L3	M5	02-Aug-2025
L4	M5	02-Jun-2025
L5	M5	02-Jul-2025

Lesson Learned Status table contains

LLID	CurrentState	LastUpdate	DueDate
L3	WIP	02-Jan-2026	31-Jan-2026
L4	WIP	02-Dec-2025	02-Dec-2025
L5	DONE	02-Jul-2025	02-Jul-2025

Output expected used for visualization or automation 

for Milestone M4/M5 need to check the LLID present in status table, if present mark it as Yes.

OpenAgeing : Calculate the ageing using DueDate and CurrentState (=WIP)

LLID	Milestone	StatusUpdated	EntryInERP	Open Ageing
L1	M1	02-Dec-2025
L2	M2	02-Dec-2025
L3	M5	02-Aug-2025	Yes	3 days
L4	M5	02-Jun-2025	Yes	60 days
L5	M5	02-Jul-2025	Yes

[cengizhanarslan · Accepted solution]

1) EntryInERP

Create this calculated column in LessonLearned:

EntryInERP =
VAR HasStatus =
    CALCULATE (
        COUNTROWS ( 'Lesson Learned Status' ),
        'Lesson Learned Status'[LLID] = LessonLearned[LLID]
    ) > 0
RETURN
IF (
    LessonLearned[Milestone] IN { "M4", "M5" } && HasStatus,
    "Yes",
    BLANK ()
)

2) Open Ageing

Open Ageing =
VAR State =
    LOOKUPVALUE (
        'Lesson Learned Status'[CurrentState],
        'Lesson Learned Status'[LLID], LessonLearned[LLID]
    )
VAR DueDate =
    LOOKUPVALUE (
        'Lesson Learned Status'[DueDate],
        'Lesson Learned Status'[LLID], LessonLearned[LLID]
    )
VAR LastUpdate =
    LOOKUPVALUE (
        'Lesson Learned Status'[LastUpdate],
        'Lesson Learned Status'[LLID], LessonLearned[LLID]
    )
VAR Days =
    IF ( State = "WIP" && NOT ISBLANK ( DueDate ) && NOT ISBLANK ( LastUpdate ),
        DATEDIFF ( LastUpdate, DueDate, DAY )
    )
RETURN
IF ( NOT ISBLANK ( Days ), Days & " days" )

[manojk_pbi]

Thanks for your reply. I am getting error when i add EntryInERP calculated column. I am not getting the table LessonLearned while setting the value to HasStatus.

[v-menakakota]

Hi @manojk_pbi ,

Thanks for reaching out to the Microsoft fabric community forum. 

This error usually occurs due to a table name or column context mismatch. Please verify the exact name of the table in the Fields pane. If the table name contains spaces (for example, Lesson Learned instead of LessonLearned).

Additionally, please check whether a relationship exists between Lesson Learned and Lesson Learned Status on LLID. If the relationship is missing or inactive, the calculation may not work as expected.

If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

Best Regards, 
Community Support Team  

[manojk_pbi]

I am getting below error when added as column. Pls advice me what is wrong here

[v-menakakota]

Hi @manojk_pbi ,

Thanks for sharing the update

Everything is actually working as expected now. The reason you were seeing a value of 2 earlier is that the visual was automatically summing the column after multiple related rows from the Lesson Learned Status table were pulled in  LLID 1 has more than one related status, so the value 1 was counted more than once. After switching the field to Don’t summarize, the table correctly shows the result per lesson learned: 1 when at least one related status record exists, and 0 when it doesn’t.

Best regards,
Community Support Team

[manojk_pbi]

Thanks for your explanation and sample. Helps lot..