I am in Orlando for the Power Summit North America 2019. So, if there is any delay from my side to work out the solution, please excuse. In any case, can you send the sample data for all the 3 tables with the expected results in the excel sheet?
Original Message:
Sent: Oct 14, 2019 08:58 PM
From: Zachary Attaran
Subject: Difficulty Showing % of events completed
@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
------------------------------
Zachary Attaran
Original Message:
Sent: Oct 10, 2019 03:17 PM
From: Gopa Kumar Sivadasan
Subject: Difficulty Showing % of events completed
Hi @Zachary Attaran
- 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
- 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.
- 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 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
------------------------------
Gopa Kumar
Limner Consulting
Original Message:
Sent: Oct 10, 2019 02:23 PM
From: Zachary Attaran
Subject: Difficulty Showing % of events completed
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
Original Message:
Sent: Oct 10, 2019 01:12 PM
From: Gopa Kumar Sivadasan
Subject: Difficulty Showing % of events completed
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:
- Filter out null values in the transaction table in query editor and then use the same DAX;
- 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:
------------------------------
Gopa Kumar
Limner Consulting
Original Message:
Sent: Oct 09, 2019 11:55 PM
From: Zachary Attaran
Subject: Difficulty Showing % of events completed
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
Original Message:
Sent: Oct 09, 2019 08:19 AM
From: Gopa Kumar Sivadasan
Subject: Difficulty Showing % of events completed
Hi @Zachary Attaran
Before we 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
Original Message:
Sent: Oct 08, 2019 07:32 PM
From: Zachary Attaran
Subject: Difficulty Showing % of events completed
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
Original Message:
Sent: Oct 08, 2019 05:18 AM
From: Gopa Kumar Sivadasan
Subject: Difficulty Showing % of events completed
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.
------------------------------
Gopa Kumar
Limner Consulting
Original Message:
Sent: Oct 04, 2019 05:48 PM
From: Zachary Attaran
Subject: Difficulty Showing % of events completed
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
------------------------------