Forum Discussion
Write logic for Calculated Column or Measure
- 6 months ago
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" )
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.
- v-menakakota6 months agoCommunity Support
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_pbi6 months agoHelper V
I am getting below error when added as column. Pls advice me what is wrong here
- v-menakakota6 months agoCommunity Support
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