Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
6 months ago
Solved

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 tab...
Need Help
Show and Tell
  • cengizhanarslan's avatar
    cengizhanarslan
    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" )
cengizhanarslan's avatar
cengizhanarslan
Super User
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" )
  • manojk_pbi's avatar
    manojk_pbi
    Helper V
    6 months ago

    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's avatar
      v-menakakota
      Community Support
      6 months ago

      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's avatar
        manojk_pbi
        Helper V
        6 months ago

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