Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gjelstrup
Helper I
Helper I

How to create a code to only publish a final date if all actions is closed

I have the below dataset, where there is several actions (Code 005) for each notification. I would like to create a column that will publish a final date for closure of the actions - but only if all actions for the notification has a comepled date. There is different number of action tasks for each notification. 

 

I need it to publish the latest date an action was closed for each notification. 

TypNotifctnCompletedCodeAction tasks closedExpected result 
ZP20001790215-jun-22000515.jun.202230 jun22
ZP20001790230-jun-22000530.jun.202230 jun22
ZP20001839405-jul-220005 05-jul-22 05-jul22
ZP20000015327-apr-21000527.apr.2021 no date as one action is not closed yet.
ZP20000015327-apr-21000527.apr.2021 no date as one action is not closed yet.
ZP200000153 0005  no date as one action is not closed yet.
ZP20000015327-apr-21000527.apr.2021 no date as one action is not closed yet.

 

I Really hope that someone can help me out here 🙂 Thank you very much in advance for your help. 

1 ACCEPTED SOLUTION

You can make a tweak to make sure that the code is also taken into account

Final actions completed =
VAR CompletedDates =
    CALCULATETABLE (
        VALUES ( QM13[Action tasks closed] ),
        ALLEXCEPT ( QM13, QM13[Notifctn], QM13[Code] )
    )
RETURN
    IF (
        NOT BLANK () IN CompletedDates,
        CALCULATE (
            MAX ( QM13[Action tasks closed] ),
            ALLEXCEPT ( QM13, QM13[Notifctn], QM13[Code] )
        )
    )

View solution in original post

4 REPLIES 4
gjelstrup
Helper I
Helper I

Thank you so much for your kind support. It works perfect now. 

 

johnt75
Super User
Super User

You can add a column like

Final completed date = 
VAR CompletedDates =
    CALCULATETABLE (
        VALUES ( 'Table'[Action tasks closed] ),
        ALLEXCEPT ( 'Table', 'Table'[Notifctn] )
    )
RETURN
    IF (
        NOT BLANK () IN CompletedDates,
        CALCULATE (
            MAX ( 'Table'[Action tasks closed] ),
            ALLEXCEPT ( 'Table', 'Table'[Notifctn] )
        )
    )

Thank you very much for your quick support. I have entered the formula into my dataset. It does not give me any errors, but it also does not give me any dates. 🤔

I think now I understand why. In my dataset there will also be other lines with codes "006" and "007" that will not be completed. Therefore it gives me empty fields in all cases. Can you advise in this more complex situation also?

TypNotifctnCompletedCodeAction tasks closedExpected result 
ZP20001790215-jun-22000515.jun.202230 jun22
ZP20001790230-jun-22000530.jun.202230 jun22
ZP200017902 0006 

 either empty or  30-jun22

ZP200017902 0007 either empty or  30-jun22
ZP20001839405-jul-220005 05-jul-22 05-jul22
ZP20001839405-jul-220006  either empty or  05-jul22
ZP20000015327-apr-21000527.apr.2021 no date as one action is not closed yet.
ZP20000015327-apr-21000527.apr.2021 no date as one action is not closed yet.
ZP200000153 0005  no date as one action is not closed yet.
ZP20000015327-apr-21000527.apr.2021 no date as one action is not closed yet
ZP200000153 0006  no date as one action is not closed yet.
ZP200000153 0007  no date as one action is not closed yet.
Final actions completed =
VAR CompletedDates =
    CALCULATETABLE (
        VALUES (QM13[Action tasks closed]),
        ALLEXCEPT (QM13,QM13[Notifctn])
    )
RETURN
    IF (
        NOT BLANK () IN CompletedDates,
        CALCULATE (
            MAX ( QM13[Action tasks closed]),
            ALLEXCEPT (QM13,QM13[Notifctn])
        )
    )

You can make a tweak to make sure that the code is also taken into account

Final actions completed =
VAR CompletedDates =
    CALCULATETABLE (
        VALUES ( QM13[Action tasks closed] ),
        ALLEXCEPT ( QM13, QM13[Notifctn], QM13[Code] )
    )
RETURN
    IF (
        NOT BLANK () IN CompletedDates,
        CALCULATE (
            MAX ( QM13[Action tasks closed] ),
            ALLEXCEPT ( QM13, QM13[Notifctn], QM13[Code] )
        )
    )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.