Forum Discussion
Getting info from 2 Tables based on Period Selection
Hello,
Below a Diagram of the DataSet in the attached Power BI Desktop sample:
Contract is a shared dimension between ContractStatuses and ContractTransactions fact tables.
The Contract has ContractAltKey as a business key with many images marked by different Seq#. ContractKey is the primary key.
If we select Period 31/12/2017, then for ContractAltKey= 719496, the latest record in ContractStatuses table below as per LastUpdateDate will be the one with ContractStatusesKey = 3958349, as marked below in orange.
If we select Period 31/12/2015, then for ContractAltKey= 719496, the latest record in ContractStatuses as per LastUpdateDate will be the one with ContractStatusesKey = 3051303, as marked below in orange.
Now after getting this record, I will need to check that the ContractStatusCode of this latest row, is not “C”.
Then join with ContractTransactions table based on ContractKey and Seq# fields (The cells marked in Black) of the most recent record based on Period filter. Example of record expected from ContractTransactions for Period 31/12/2015
Then Retrieve corresponding Amount 1 or do other calculations on those records in Contract Transactions.
N.B. This can become more complex in the sense that Contract Transactions fact table might contain multiple records matching the ContractKey and Seq#, and we need to get the Maximum(TransactionKey) that satisfies the join condition. As a first problem, I'm looking into possibility of retrieving Amount 1 based on above logic.
Link to the attached the Sample Power BI Sample Power BI Desktop
Many thanks for any suggestion on the approach to take.
Grace
Hi Again,
In case someone can help, I have updated the Sample PBIX file and added sample measures I have been trying and explaining expected results.
Mainly, 2 measures are added under ContractStatusesKey Fact table:
- To get the maximum date selected on dashboard
MaxDate = Max(DimTime[PK_Date])
- To calculate the Maximum ContractStatusesKey[ContractStatusesKey] based on the Date selected as well as other conditions
MaxActiveKey = CALCULATE(Max(ContractStatusesKey[ContractStatusesKey]),Filter(ContractStatusesKey, ((ContractStatusesKey[LastUpdatedDate]<=[MaxDate]) && ( ((ContractStatusesKey[ContractStatusCode] <> "C") && (ContractStatusesKey[ContractstatusDate] <= [MaxDate]) ) || ((ContractStatusesKey[ContractStatusCode] ="C") && (ContractStatusesKey[ContractStatusDate] <= [MaxDate])) )) ))
On the Fact Contract Transactions, one measure is created to flag if the Contract Transaction belongs to the most recent Contract Statuses Key based on the measure [MaxActiveKey] defined above, and after retrieving that row, filter those with Status Code <>"C" as marked below:
- TransactionForActiveContract = CALCULATE(DISTINCTCOUNT(ContractTransactions[ContractKey]),Filter(ContractTransactions, COUNTROWS(FILTER(ContractStatusesKey, COUNTROWS(Filter(( AddColumns(Summarize(ContractStatusesKey, ContractStatusesKey[ContractKey]), "MaxKey", [MaxActiveKey])), EARLIER(ContractStatusesKey[ContractStatusesKey]) = [MaxKey]) )>0 && ContractStatusesKey[ContractKey] = EARLIER(ContractTransactions[ContractKey]) && EARLIER(ContractTransactions[TransactionAltKey]) = ContractStatusesKey[Seq#] && ContractStatusesKey[ContractStatusCode]<>"C" ))>0 ))
Results as displayed in attached pbix, is not filtering the Contract Transaction fact based on the correct MaxActiveKey:
Really appreciate any guidance,
Thank You
Grace