Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors