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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors