Forum Discussion

gracemanasseh's avatar
gracemanasseh
Frequent Visitor
7 years ago

Getting info from 2 Tables based on Period Selection

Hello,

 

I have 2 Tables that I want to get info from.

Table 1 =Transaction Table that is linked to Dimension Contract. It contains multiple Transactions per Contract.

Table 2 = Contract Statuses is linked to Dimension Contract. It also contains multiple records (Statuses) per Contract.

 

I am looking for a way to be able to filter Table 2 (Contract Statuses) based on Max Period filter selected by user on the Dashboard in order to return the most recent Contract Status records per ContractKey, based on the Period Selected on the report.

Then based on the above retrieved records from Contract Statuses Table, do a join with Table 1 (the join will include several columns to check) in order to return the corresponding Transaction Amounts.

 

I was thinking if in Table 2, the Contract Statuses, I can created a calculated column using RankX to Rank the Contracts based on their Dates that fall in the Period Selected on the report and then do a filter on Tabe 1 based on the contract Status with Rank 1 for example. However, the calculated column doesn't seem to get affected by the Period Selection. It always Ranks Contracts based on all the Dates. 

 

Here's the calculated column I was trying:

=RANKX ( FILTER ( ContractStatuses, (ContractStatuses[ContractAltKey]= EARLIER ContractStatuses[ContractAltKey]  && ContractStatuses[LastUpdatedDate] <=Max(DimTime[PK_Date]) )), ContractStatuses[ContractStatusesHistKey], , 0,DENSE)

 

I am assuming it's not appropriate to use the Max(DimTime[]) in a calculated column?

 

On another hand, if I try to calculate a measure using the Max(Period), the measure seems to be affected correctly by the Period. But not sure how to proceed to compete the DAX to reach the values from Table 1 by joining on Table 2 specific columns where ContractStatusesKey is in the Maximum from the below measure.

 

Measure:=CALCULATE(Max(ContractStatuses[ContractStatusesKey]),Filter(ContractStatuses, (ContractStatuses[LastUpdatedDate]<=Max(DimTime[PK_Date])  )) )

 

Appreciate any guidance on this matter.

 

Thank You in advance

Grace

 

 

 

 

Need Help

3 Replies

    • gracemanasseh's avatar
      gracemanasseh
      Frequent Visitor
      7 years ago

      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

      • gracemanasseh's avatar
        gracemanasseh
        Frequent Visitor
        7 years ago

        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.

        PowerBICommunityQuest_Sample

         

        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