Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Are joins involving date ranges possible when merging?
I have two data sources that will help me count the number of business days between a single appointment's [Appt Made Date] and [Appt Visit Date]
Table 1. Scheduled Appts: [Appt Key], [Appt Made Date], [Appt Visit Date]
Table 2. Date Dimension: [Date], [Holiday Flag], [Weekend Flag]
Ideally I want to merge these two data sources like so
Select
S.[Appt Key], Count(D.Date)
FROM Scheduled Appts S
LEFT OUTER JOIN Date Dimension D ON D.[DATE] BETWEEN S.[Appt Made Date] AND S.[Appt Visit Date]
WHERE [Holiday Flag] IS NULL AND [Weekend Flag] IS NULL
GROUP BY S.[Appt Key]
Is something like that allowed with merging or appending?
Solved! Go to Solution.
Seems like there might be the potential to do some more sophisticated joining with Table.Join but there is no documentation on the joinAlgorithm parameter:
https://msdn.microsoft.com/en-us/library/mt260788.aspx
Hi @Anonymous,
smoupre's solution seems well, but if you use the sql server database, you can directly write the T-SQL query at sql statement option:
Notice: "DirectQuery" mode has some limitations with sql query.
Regards,
Xiaoxin Sheng
Hi @Anonymous,
smoupre's solution seems well, but if you use the sql server database, you can directly write the T-SQL query at sql statement option:
Notice: "DirectQuery" mode has some limitations with sql query.
Regards,
Xiaoxin Sheng
Seems like there might be the potential to do some more sophisticated joining with Table.Join but there is no documentation on the joinAlgorithm parameter:
https://msdn.microsoft.com/en-us/library/mt260788.aspx
User | Count |
---|---|
98 | |
90 | |
84 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |