Forum Discussion
Write logic for Calculated Column or Measure
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 |
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" )
6 Replies
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" )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.
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
