Power BI Exchange

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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.

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

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

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

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.

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.

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

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

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

2. 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.

3. 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.

IS Complete Indicator =--------------------------------------//calculate the date upto which the records need to be selectedVAR _futureDate =    EDATE ( TODAY (), 24 ) --------------------------------------//filter the events table for records on or before the future data and on or before Today to avoid past recordsVAR __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 tableVAR __rlvntable2 =    SUMMARIZE (        __rlvntable,        Events[TenureKey],        "MinDate", MIN ( Events[EventNoticeDate] )    ) -----------------------------------------find the minimum date for each tenure keyVAR _mindate =    MINX ( __rlvntable2, [MinDate] ) -----------------------------------------find the minimum EventIDVAR _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 blankVAR _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 tableVAR __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 tableVAR __rlvntable4 =    CALCULATETABLE ( Tasks, TREATAS ( __rlvntable3, Tasks[Transaction ID] ) ) ---------------------------------------//get the relevant Task and the Completion status from the relevant filtered tableVAR _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

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

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.

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.

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

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

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

2. 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.

3. 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.

IS Complete Indicator =--------------------------------------//calculate the date upto which the records need to be selectedVAR _futureDate =    EDATE ( TODAY (), 24 ) --------------------------------------//filter the events table for records on or before the future data and on or before Today to avoid past recordsVAR __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 tableVAR __rlvntable2 =    SUMMARIZE (        __rlvntable,        Events[TenureKey],        "MinDate", MIN ( Events[EventNoticeDate] )    ) -----------------------------------------find the minimum date for each tenure keyVAR _mindate =    MINX ( __rlvntable2, [MinDate] ) -----------------------------------------find the minimum EventIDVAR _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 blankVAR _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 tableVAR __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 tableVAR __rlvntable4 =    CALCULATETABLE ( Tasks, TREATAS ( __rlvntable3, Tasks[Transaction ID] ) ) ---------------------------------------//get the relevant Task and the Completion status from the relevant filtered tableVAR _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

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

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.

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.

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

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

@Gopa Kumar Sivadasan ​​

Gopa,

It seems I have one follow up question. Previously, we were able to summarize by Min ( Events_[Event Notice Date] ) because there was only one event to cosnsider (Event Type Key 7).

Now, I have to consider two Event Type Key's, each with differing filter conditions.

I need to select the minimum date from the following conditions, grouped TenureKey Wise:

For each tenure key, Select the MAX date of event type key = "2", grouped tenure Keywise

For each tenure key, Select the MIN date of event type key = "7", grouped tenure Keywise

From that, we should have at least one event date for each tenure key, at most two if there is a value in both event type keys.

From there, I would like to find the minimum Event date.

I have tried doing this by Summarizing again, but it does not seem to work. Any insight you can give would be helpful. I have attached a dataset once more.

Thanks,

Zack

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

2. 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.

3. 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.

IS Complete Indicator =--------------------------------------//calculate the date upto which the records need to be selectedVAR _futureDate =    EDATE ( TODAY (), 24 ) --------------------------------------//filter the events table for records on or before the future data and on or before Today to avoid past recordsVAR __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 tableVAR __rlvntable2 =    SUMMARIZE (        __rlvntable,        Events[TenureKey],        "MinDate", MIN ( Events[EventNoticeDate] )    ) -----------------------------------------find the minimum date for each tenure keyVAR _mindate =    MINX ( __rlvntable2, [MinDate] ) -----------------------------------------find the minimum EventIDVAR _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 blankVAR _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 tableVAR __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 tableVAR __rlvntable4 =    CALCULATETABLE ( Tasks, TREATAS ( __rlvntable3, Tasks[Transaction ID] ) ) ---------------------------------------//get the relevant Task and the Completion status from the relevant filtered tableVAR _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

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

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.

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.

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

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