Help with DirectQuery and RELATED / LOOKUPVALUE alternatives

[DavidAtkins]

Hi

I have a report that uses DirectQuery to connect to an Azure database and I'm having trouble when trying to create a calculated column in one table that uses data from another table. If we weren't using DirectQuery then I think it would be really simple using LOOKUPVALUE but that's not available in DQ.

A very simplified version of my dataset is as follows (in reality these tables are much, much larger in terms of columns and rows):

DimTable		FactTable1			FactTable2
Key		Key	Returns Period		Key	Date
-1		13	15		-1	01/01/1980
1		14	15		1	01/01/2020
2		15	30		2	02/01/2020
3		16	15		3	03/01/2020
4		17	28		4	04/01/2020
5		18	28		5	05/01/2020
6		19	15		6	06/01/2020
7		20	5		7	07/01/2020
8		-1	5		8	08/01/2020
9		-1	10		9	09/01/2020
10		-1	15		10	10/01/2020
11					11	11/01/2020
12					12	12/01/2020
13					13	13/01/2020
14					14	14/01/2020
15					15	15/01/2020
16
17
18
19
20

The two Fact tables are connected to the Dim table with one-to-many relationships (the Dim Table being the "1" side). What I want to do is get the returns period column from Fact1 into Fact2 somehow so that I can create another column called "Return Date" which would be the Date in that table plus the return period. For instance for the row with key 13, the return date would be 13/01/2020 + 15 days.

I've tried a few different ways to do this but had no success. LOOKUPVALUE would be really easy but isn't available in DQ. I thought about using an inactive relationship between Fact1 and Fact2 and using USERELATIONSHIP but couldn't figure that out. The one-to-many relationships seem to rule out a RELATED column between Fact1 and Fact2. I also tried to use a summary table using the code below, connecting it to Fact2, and then using RELATED to get the returns period into a calculated column in Fact2 but RELATED doesn't work between a DQ table and a calculated table (NB the 28 is in there because I want the returns period to default to 28 if there is no value in Fact1).

SummaryReturnsPeriod = 
CALCULATETABLE (
    SUMMARIZE (
        DimTable,
        DimTable[Key],
        "Returns Period",
            IF (
                ISBLANK (
                    LOOKUPVALUE (
                        FactTable1[Returns Period],
                        FactTable1[Key], DimTable[Key]
                    )
                ),
                28,
                LOOKUPVALUE (
                    FactTable1[Returns Period],
                    FactTable1[Key], DimTable[Key]
                )
            )
    ),
    DimTable[Key] <> -1
)

 Any help with this would be greatly appreciated!

[Icey · Accepted solution]

Hi @DavidAtkins ,

Try this:

Measure = 
VAR RelatedDate_ =
    CALCULATE (
        MAX ( 'FactTable2$'[Date] ),
        TREATAS ( VALUES ( 'FactTable1$'[Key] ), 'FactTable2$'[Key] ),
        'FactTable2$'[Key] <> -1
    )
RETURN
    IF (
        RelatedDate_ <> BLANK (),
        RelatedDate_ + MAX ( 'FactTable1$'[Returns Period] )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DavidAtkins]

Hi @Icey

Thanks for this - it works with small amounts of data but when I pull back all the data I need, it can't display the results as it runs out of memory.

Is there a way to get the Returns Period in FactTable2 as a calculated column instead?

[DavidAtkins · Accepted solution]

NB The only way I've found to get this to work is to make a summary table like below. I'm sure it's not best practice but it works...

SummaryReturns =
SUMMARIZE (
    FactTable2,
    FactTable2[Key],
    FactTable2[Date],
    "Return Period",
        IF ( FactTable2[Key] = -1, 28,
            IF (
                ISBLANK ( LOOKUPVALUE ( FactTable1[Returns Period], FactTable1[Key], FactTable2[Key] )
                ),
                28,
                LOOKUPVALUE ( FactTable1[Returns Period], FactTable1[Key], FactTable2[Key] )
            )
        )
)

[Icey · Accepted solution]

Hi @DavidAtkins ,

Try this:

Measure = 
VAR RelatedDate_ =
    CALCULATE (
        MAX ( 'FactTable2$'[Date] ),
        TREATAS ( VALUES ( 'FactTable1$'[Key] ), 'FactTable2$'[Key] ),
        'FactTable2$'[Key] <> -1
    )
RETURN
    IF (
        RelatedDate_ <> BLANK (),
        RelatedDate_ + MAX ( 'FactTable1$'[Returns Period] )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.