Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Difficulty Showing % of events completed

Jump to Best Answer
  • 1.  Difficulty Showing % of events completed

    Posted 8 days ago
    Hello,

    Having trouble showing percentage of events completed due to some quirks in my data. Allow me to explain the full set of problems as they relate to ​real estate which may require some background:


    In real estate leases, there are often options to extend your term prior to the lease expiration. I want to know how many leases in my data set have these options, an event called "Option - Latest Renewal Date", within the next 24 months (from today). The key point here is that it is entirely possible to have 2 of these events within that 24 month span per lease. I only want to count the soonest "Option - Latest Renewal Date" as part of this dashboard. The data for this is being extracted from the attached "Events" table.

    Once I know how many of the leases in my data set have an event within 24 months, I want to know whether or not work has been initiated to renew that lease. This can be determined by reviewing the Completion Status of a specific task named "SOR Approval" as pulled from the column "Task Status" from the "Tasks" table.

     

    The connecting table between "Tasks" & "Events" is "Transactions". The relationships can be reviewed in the attached Power BI file.

     

    Ultimately, it can be boiled down to this:

     

    1) Not all Tenure Keys that have at least one "Option - Latest Renewal Date" necessarily have a transaction associated with that tenure key.

     

    2) If a transaction does exist, and the task "SOR Approval" is 'complete', this means that the most imminent critical date has been acted upon. It does not mean all of the critical dates for that tenure key have been acted upon. This applies for the opposite as well if the task is listed as 'not completed'.

    This is multiple date issue is what I suspect I need to solve for. Creating a DAX table to filter by my 24 month requirement, creating aliases for "blank" completion status- these are all things I think I can solve. I'm just stumped on how to filter for the earliest date per Tenure Key. Right now, my dashboard represents the data as follows:

    Complete: 10
    Not Complete: 11
    Blank: 8

    This is due to there being multiple "dates" which Power BI thinks are "complete". In reality, it's fine if Power BI thinks this. I just need to figure out how to filter the data so that the total number of lease events in my pool consists of only the earliest lease event that falls within a 24 month period from today.

    The other way I've considered going about it, is finding some way to make the task approval status unique to just the lease event in question. I don't know if there is a way to do that with my data, however. Regardless, this is the representation of the data I am expecting:

    Complete: 5
    Not Complete: 8

    Tenure Key Event Date Completion Status
    1 3/31/2020 Complete
    2 3/31/2021 Not Complete
    3 10/31/2019 Complete
    4 9/30/2020 Not Complete
    5 11/30/2019 Complete
    6 6/30/2020 Not Complete
    7 5/30/2021 Complete
    8 12/30/2019 Not Complete
    9 2/27/2020 Complete
    10 6/30/2021 Not Complete
    11 11/30/2019 Not Complete
    12 2/29/2020 Not Complete
    13 5/30/2020 Not Complete




    Thanks in advance for any help! This one has really stumped me (and everyone at my organization) so appreciate any insight you may be able to give.

    - Zack



    ------------------------------
    Zachary Attaran
    ------------------------------

    Attachment(s)

    xlsx
    Sample Data.xlsx   13K 1 version
    Conference-PBI_200x200


  • 2.  RE: Difficulty Showing % of events completed

    Top Contributor
    Posted 5 days ago
    Edited by Gopa Kumar Sivadasan 5 days ago
      |   view attached
    Hi @Zachary Attaran

    Please see if the following helps you.


    I have done it through a calculated column in the 'Events' Table. The DAX for the same is below:

    IS Complete Indicator =
    --------------------------------------
    //calculate the date upto which the records need to be selected
    VAR _futureDate =
        EDATE ( TODAY (), 24 ) 
    --------------------------------------
    //filter the events table for records on or before the future data and on or before Today to avoid past records
    VAR __rlvntable =
        CALCULATETABLE (
            Events,
            ALLEXCEPT ( Events, Events[TenureKey] ),
            Events[EventNoticeDate] >= TODAY ()
                && Events[EventNoticeDate] <= _futureDate
        ) 
    //create a summary table tenure keywise with the minimum date from the filtered table
    VAR __rlvntable2 =
        SUMMARIZE (
            __rlvntable,
            Events[TenureKey],
            "MinDate", MIN ( Events[EventNoticeDate] )
        ) 
    ---------------------------------------
    --find the minimum date for each tenure key
    VAR _mindate =
        MINX ( __rlvntable2, [MinDate] ) 
    ---------------------------------------
    --identify the records for each valid tenure which has the minimum date 
    VAR _mindateIndicator =
        IF ( Events[EventNoticeDate] = _mindate, 1, BLANK () ) 
    ---------------------------------------
    RETURN
        --find the status for only the records which has the minimum date and is SOR approved. IF no record in related table then mark it as not complete and if the tenure key is not part of the filtered table, then leave it as blank
        SWITCH (
            TRUE (),
            _mindateIndicator = 1
                && RELATED ( Tasks[Task] ) = "SOR Approval", RELATED ( Tasks[Completion Status] ),
            _mindateIndicator = 1
                && ISBLANK ( RELATED ( Tasks[Task] ) ), "Not Complete",
            BLANK ()
        )
    ​


    I have then filtered the visuals for blank values as below.



    PFA the pbix file for your reference.


    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Difficulty Showing % of events completed

    Posted 4 days ago
    Edited by Zachary Attaran 4 days ago
      |   view attached
    Gopa,

    Thank you so much for showing me how to do this. This has been so informative.

    For whatever reason, my real data set has many to many relationship between Transactions & Events across TenureKey. I tried to create the sample data so that it would have many to many relationship as well, however it still ended up being one to many...

    All that said, I've attached a revised Sample Data set. This one is many to many between Table & Events across TenureKey.

    Is it still possible to create a calculated column with this type of relationship? I receive error the error:

    "The column 'Tasks[Task]' either doesn't exist or doesn't have a relationship to any table available in the current context."

    I've tried replacing RELATED with RELATEDTABLE, but I'm not entirely sure I understand how that works or if that is the right way to resolve.

    I'm also seeing some suggestions to create a third table as a solution, but I'm not really sure what constitutes a unique tenureKey to satisfy working around many to many in this scenario...

    The correct solution to the attached data set should be:

    Completed: 2
    Not Completed: 286

    I've included how I arrived at that solution in the workbook.

    Really appreciate your help again. Thanks.

    ------------------------------
    Zachary Attaran
    ------------------------------

    Attachment(s)

    xlsx
    Sample Data V2.xlsx   123K 1 version
    Conference-PBI_200x200


  • 4.  RE: Difficulty Showing % of events completed

    Top Contributor
    Posted 4 days ago
    Edited by Gopa Kumar Sivadasan 3 days ago
    Hi @Zachary Attaran

    Before going into a solution, I had a look at your new data. Shall appreciate if you can clarify the following:

    Tenure Keys 418 & 6861 are duplicates in the Transaction Table and each one has different Transaction IDs. They are separate entries in the Tasks table also. So, if they can have different completion status in the Tasks table, how do we determine which status to take for the relevant Tenure Key?


    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Difficulty Showing % of events completed

    Posted 3 days ago
      |   view attached
    Hi @Gopa Kumar Sivadasan

    Thanks for following up. It looks like those tenure keys / transactions are duplicates. I've actually done a little digging, and they can be removed from the data source all together, which I've done on the attached. For some reason I thought this would solve the many to many relationship issue, but it does not appear that is the case.

    It will always be true that there should only be one transaction for each tenure key. In the future, if the end user creates a duplicate transaction for a tenure key, it will need to be deleted in the data source.

    Hope that clarifies, please let me know if you need more information.

    Thanks

    -Zack

    ------------------------------
    Zachary Attaran
    ------------------------------

    Attachment(s)

    xlsx
    Sample Data V3.xlsx   123K 1 version
    Conference-PBI_200x200


  • 6.  RE: Difficulty Showing % of events completed

    Top Contributor
    Posted 3 days ago
    Edited by Gopa Kumar Sivadasan 3 days ago
    Hi @Zachary Attaran

    Thanks for the clarification. You are getting the many to many relationship option only because of the multiple null values in the Transactions table.


    You have two options:

    1. Filter out null values in the transaction table in query editor and then use the same DAX;
    2. If for any reason you cannot filter out the null values in the Transaction table, then you will have to have a bridging table and then modify the DAX.

    In Option 2, the bridging table is created in query editor and the relationship created as below:


    Then, use the following DAX:
    IS Complete Indicator =
    --------------------------------------
    //calculate the date upto which the records need to be selected
    VAR _futureDate =
        EDATE ( TODAY (), 24 ) 
    --------------------------------------
    //filter the events table for records on or before the future data and on or before Today to avoid past records
    VAR __rlvntable =
        CALCULATETABLE (
            Events,
            ALLEXCEPT ( Events, Events[TenureKey] ),
            Events[EventNoticeDate] >= TODAY ()
                && Events[EventNoticeDate] <= _futureDate
        ) 
    //create a summary table tenure keywise with the minimum date from the filtered table
    VAR __rlvntable2 =
        SUMMARIZE (
            __rlvntable,
            Events[TenureKey],
            "MinDate", MIN ( Events[EventNoticeDate] )
        ) 
    ---------------------------------------
    --find the minimum date for each tenure key
    VAR _mindate =
        MINX ( __rlvntable2, [MinDate] ) 
    ---------------------------------------
    --identify the records for each valid tenure which has the minimum date. Ignore if the event notice date is blank
    VAR _mindateIndicator =
        IF (
            ISBLANK ( _mindate ),
            BLANK (),
            IF ( Events[EventNoticeDate] = _mindate, 1, BLANK () )
        ) 
    ---------------------------------------
    //filter the tasks table based on the Tenure key in the Events Table
    --first filter the transaction table for transaction IDs based on tenure key in the events table
    VAR __rlvntable3 =
        CALCULATETABLE (
            VALUES ( Transactions[Transaction ID] ),
            TREATAS ( VALUES ( Events[TenureKey] ), Transactions[TenureKey] )
        ) 
    --now filter the Tasks table based on the relevant transaction ID from the Transaction table
    VAR __rlvntable4 =
        CALCULATETABLE ( Tasks, TREATAS ( __rlvntable3, Tasks[Transaction ID] ) ) 
    ---------------------------------------
    //get the relevant Task and the Completion status from the relevant filtered table
    VAR _Task =
        CALCULATE ( SELECTEDVALUE ( Tasks[Task] ), __rlvntable4 )
    VAR _CompletionStatus =
        CALCULATE ( SELECTEDVALUE ( Tasks[Completion Status] ), __rlvntable4 ) 
    ---------------------------------------
    RETURN
        --find the status for only the records which has the minimum date and is SOR approved. IF no record in related table then mark it as not complete and if the tenure key is not part of the filtered table, then leave it as blank
        SWITCH (
            TRUE (),
            _mindateIndicator = 1
                && _Task = "SOR Approval", _CompletionStatus,
            _mindateIndicator = 1
                && ISBLANK ( _Task ), "Not Completed",
            BLANK ()
        )
    ​


    In your sample3 data Tenure key 452 is duplicated in the events table with the same event notice date. In this case both records will show. Also, Tenure Key 620 has no event notice date. I have modified the DAX to exclude such cases.

    PFA the pbix files with the two options.



    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Difficulty Showing % of events completed

    Posted 3 days ago

    Hi @Gopa Kumar Sivadasan


    Thank you- once again!

    Some clarification- When you say DAX is modified to exclude such cases, you are referring to just where Tenure Key has no Event Notice Date, correct?

    Regarding Tenure Key's that have a duplicate event notice date, is it possible to then modify DAX to select based off minimum EventID in this scenario only?

    I found the following TenureKey's with duplicate Event Notice Date:

    452
    751
    1944
    2010
    6256
    6736

    Removing the duplicate "Not Completed" event would change the correct solution to:

    Completed: 2
    Not Completed: 234

    Finally- what are the factors that determine which TenureKey's go into the Bridging Table in Option 2? In reading about key tables for many to many relationships, I understand it to be the list of unique values being bridged. In this case, I would expect there to be 236 unique tenure keys, however there are only 190 in the bridging table.

    Thank you!

    -Zack



    ------------------------------
    Zachary Attaran
    ------------------------------

    Conference-PBI_200x200


  • 8.  RE: Difficulty Showing % of events completed
    Best Answer

    Top Contributor
    Posted 3 days ago
    Hi @Zachary Attaran

    1. Some clarification- When you say DAX is modified to exclude such cases, you are referring to just where Tenure Key has no Event Notice Date, correct?

    Yes

    1. Regarding Tenure Key's that have a duplicate event notice date, is it possible to then modify DAX to select based off minimum EventID in this scenario only?

    DAX is modified to select based on the minimum event ID_1 in cases where there are duplicate values of minimum event date for the same Tenure.

    1. Finally- what are the factors that determine which TenureKey's go into the Bridging Table in Option 2? In reading about key tables for many to many relationships, I understand it to be the list of unique values being bridged. In this case, I would expect there to be 236 unique tenure keys, however there are only 190 in the bridging table.

    If you look at the query editor, the bridging table is created from the Transaction Table and not the Event Table. The Transaction Table has 190 unique valid values.

    Please see if the revised solution helps to resolve your issue.


    Updated DAX for Option 2:

    IS Complete Indicator =
    --------------------------------------
    //calculate the date upto which the records need to be selected
    VAR _futureDate =
        EDATE ( TODAY (), 24 ) 
    --------------------------------------
    //filter the events table for records on or before the future data and on or before Today to avoid past records
    VAR __rlvntable =
        CALCULATETABLE (
            Events,
            ALLEXCEPT ( Events, Events[TenureKey] ),
            Events[EventNoticeDate] >= TODAY ()
                && Events[EventNoticeDate] <= _futureDate
        ) 
    //create a summary table tenure keywise with the minimum date from the filtered table
    VAR __rlvntable2 =
        SUMMARIZE (
            __rlvntable,
            Events[TenureKey],
            "MinDate", MIN ( Events[EventNoticeDate] )
        ) 
    ---------------------------------------
    --find the minimum date for each tenure key
    VAR _mindate =
        MINX ( __rlvntable2, [MinDate] ) 
    ---------------------------------------
    --find the minimum EventID
    VAR _minEventID =
        CALCULATE (
            MIN ( Events[EventID_1] ),
            ALLEXCEPT ( Events, Events[TenureKey] ),
            Events[EventNoticeDate] = _mindate
        ) 
    ---------------------------------------
    --identify the records for each valid tenure which has the minimum date. Ignore if the event notice date is blank
    VAR _mindateIndicator =
        IF (
            ISBLANK ( _mindate ),
            BLANK (),
            IF (
                Events[EventNoticeDate] = _mindate
                    && Events[EventID_1] = _minEventID,
                1,
                BLANK ()
            )
        ) 
    ---------------------------------------
    //filter the tasks table based on the Tenure key in the Events Table
    --first filter the transaction table for transaction IDs based on tenure key in the events table
    VAR __rlvntable3 =
        CALCULATETABLE (
            VALUES ( Transactions[Transaction ID] ),
            TREATAS ( VALUES ( Events[TenureKey] ), Transactions[TenureKey] )
        ) 
    --now filter the Tasks table based on the relevant transaction ID from the Transaction table
    VAR __rlvntable4 =
        CALCULATETABLE ( Tasks, TREATAS ( __rlvntable3, Tasks[Transaction ID] ) ) 
    ---------------------------------------
    //get the relevant Task and the Completion status from the relevant filtered table
    VAR _Task =
        CALCULATE ( SELECTEDVALUE ( Tasks[Task] ), __rlvntable4 )
    VAR _CompletionStatus =
        CALCULATE ( SELECTEDVALUE ( Tasks[Completion Status] ), __rlvntable4 ) 
    ---------------------------------------
    RETURN
        --find the status for only the records which has the minimum date and is SOR approved. IF no record in related table then mark it as not complete and if the tenure key is not part of the filtered table, then leave it as blank
        SWITCH (
            TRUE (),
            _mindateIndicator = 1
                && _Task = "SOR Approval", _CompletionStatus,
            _mindateIndicator = 1
                && ISBLANK ( _Task ), "Not Completed",
            BLANK ()
        )
    ​


    PFA the two pbix files with the revised DAX



    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Conference-PBI_200x200


  • 9.  RE: Difficulty Showing % of events completed

    Posted 2 days ago
    @Gopa Kumar Sivadasan

    Thank you so much for the help. This worked perfectly. The Switch function in particular is a lifesaver. Here's an article I found about it for anyone who is not in the know:

    https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/


    Thanks again!​​

    ------------------------------
    Zachary Attaran
    ------------------------------

    Conference-PBI_200x200