Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to 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.
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] )
)
)
)
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] )
)
)
)
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
16 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |