DAX create new calculated table by joinging unrelated tables

[scabral]

Hi,

i have a 2017 SSAS Tabular model with the following tables and relationships:

we just imported a new FactLoss table from another source and need to created a new calculated table joining all tables together.  the only column in FactLoss that we can use is the LocId and we also have to make sure the Loss Reported Date is between the Policy Effetive and Exipiration dates from DimPolicy.

here are some sample rows from within a certain loss:

the new calculated tabe will need to look like this once we join the tables together:

the loss has a LocId that exists in DimCbi.  The LocId is also related to 2 different Policies that exist in DimPolicy.  They all come together in FactValues.  Also, the loss reported date is between the policy effective and expiration dates for the policies that are related to teh location.

i first thought about creating a variable to hold the existing data using summarize and then somehow trying to use DAX inner join or treatas to join the loss data using LocId and the Loss Reported Dates, but not sure how to do it just yet.  looking for some suggestions.

thanks

Scott

[v-shex-msft · Accepted solution]

Hi @scabral ,

You can use following calculate table formula to summarise multiple table records based filters conditions:

Table = 
VAR listLocYearKey =
    CALCULATETABLE (
        VALUES ( DimLocation[LocYearKey] ),
        FILTER (
            ALL ( DimLocation ),
            [Locld] & "/"
                & [Year]
                    IN SELECTCOLUMNS (
                        ALL ( 'Fact Loss'[Locld], 'Fact Loss'[Year] ),
                        "Contate", [Locld] & "/" & [Year]
                    )
        )
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CALCULATETABLE (
                FactValues,
                FILTER ( ALL ( FactValues ), [LocYearKey] IN listLocYearKey )
            ),
            "PolNumber", LOOKUPVALUE (
                DimPolicy[PolNumber],
                DimPolicy[PolYearKey], FactValues[PolYearKey]
            ),
            "PoIEffDt", LOOKUPVALUE (
                DimPolicy[PoIEffDt],
                DimPolicy[PolYearKey], FactValues[PolYearKey]
            ),
            "PoIExpDt", LOOKUPVALUE (
                DimPolicy[PoIExpDt],
                DimPolicy[PolYearKey], FactValues[PolYearKey]
            ),
            "LocName", LOOKUPVALUE (
                DimLocation[LocName],
                DimLocation[LocYearKey], FactValues[LocYearKey]
            ),
            "Locld", LOOKUPVALUE (
                DimLocation[Locld],
                DimLocation[LocYearKey], FactValues[LocYearKey]
            ),
            "Lossld", LOOKUPVALUE (
                'Fact Loss'[Lossld],
                DimLocation[Locld], LOOKUPVALUE (
                    DimLocation[Locld],
                    DimLocation[LocYearKey], FactValues[LocYearKey]
                )
            ),
            "LossAmt", LOOKUPVALUE (
                'Fact Loss'[LossAmt],
                DimLocation[Locld], LOOKUPVALUE (
                    DimLocation[Locld],
                    DimLocation[LocYearKey], FactValues[LocYearKey]
                )
            )
        ),
        "Year", [Year],
        "PolYearKey", [PolYearKey],
        "LocYearKey", [LocYearKey],
        "Locld", [Locld],
        "LocName", [LocName],
        "PolNumber", [PolNumber],
        "PoIEffDt", [PoIEffDt],
        "PoIExpDt", [PoIExpDt],
        "Lossld", [Lossld],
        "LossAmt", [LossAmt],
        "Value", [Value],
        "Premium", [Premium]
    )

Notice: I also attach my sample file below.

Regards,

Xiaoxin Sheng