cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidAtkins
Frequent Visitor

Help with DirectQuery and RELATED / LOOKUPVALUE alternatives

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 KeyReturns Period KeyDate
-1 1315 -101/01/1980
1 1415 101/01/2020
2 1530 202/01/2020
3 1615 303/01/2020
4 1728 404/01/2020
5 1828 505/01/2020
6 1915 606/01/2020
7 205 707/01/2020
8 -15 808/01/2020
9 -110 909/01/2020
10 -115 1010/01/2020
11    1111/01/2020
12    1212/01/2020
13    1313/01/2020
14    1414/01/2020
15    1515/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!

2 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

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] )
    )

Icey_0-1654485711434.png

 

 

Best Regards,

Icey

 

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

View solution in original post

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] )
            )
        )
)

 

 

View solution in original post

3 REPLIES 3
DavidAtkins
Frequent Visitor

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?

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
Community Support
Community Support

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] )
    )

Icey_0-1654485711434.png

 

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.