DAX running total optimization : Using date column from dimension vs date from fact

Greg_Deckler Thank you so much for your time. Few questions :

Is it a good practice to use dates from fact table instead of dimension table?  My understanding is that is should not matter because they are connected but what is the best practice here?

When I used date from productivity fact table and use Max(Productivity[Date]), measure seems to be running forever. So I guess I am back at the starting point. The operations ended with this message :

Details :

Feedback Type:
Frown (Error)

Timestamp:
2022-08-22T13:53:50.2656188Z

Local Time:
2022-08-22T09:53:50.2656188-04:00

Session ID:
4301c49c-83dc-4605-b6a1-ae8400c41578

Release:
August 2022

Product Version:
2.108.825.0 (22.08) (x64)

Error Message:
There's not enough memory to complete this operation. Please try again later when there may be more memory available.

OS Version:
Microsoft Windows NT 10.0.19044.0 (x64 en-US)

CLR Version:
4.8 or later [Release Number = 528372]

Peak Virtual Memory:
54.9 GB

Private Memory:
984 MB

Peak Working Set:
1.22 GB

IE Version:
11.789.19041.0

User ID:
618d1710-c07c-4212-80d4-377575658fee

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: False.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\tvaishnav\Microsoft\Power BI Desktop Store App\FrownSnapShot3f16b3a4-2eae-4226-8870-2d458ebaff1e.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\tvaishnav\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_enableWebView2
PBI_mobileAuthoringFormattingUI
PBI_sparklines

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_compositeModelsOverAS
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_scorecardVisual
PBI_fieldParametersSuperSwitch

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
150%

Supported Services:
Power BI

Formulas:


section Section1;

shared PRHours = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint", [Query="SELECT e.PRCo  as CompanyKey#(lf),CAST(e.PRCo as varchar) + ' | ' +cast(e.Employee as varchar) + ' | ' + cast(h.PRGroup as varchar) AS EmployeeKey#(lf),cast(e.PRCo as varchar) + ' | '+ cast(h.EarnCode AS varchar) AS EarnCodeKey#(lf),LTRIM(RTRIM(e.PRCo))+' | '+LTRIM(RTRIM(h.Job)) AS ContractKey#(lf),LTRIM(RTRIM(h.Phase)) + ' | ' + jp.Description AS Phase#(lf),cast(e.PRCo as varchar)+ ' | ' + ltrim(rtrim(ISNULL(h.Craft,e.Craft)))+' | ' +ISNULL(h.Class,e.Class) AS Trade#(lf),CAST(e.PRCo as varchar) + ' | ' + LTRIM(RTRIM(h.Equipment)) AS EquipmentKey#(lf),PREndDate DateKey#(lf),CASE WHEN h.Job IS NULL AND h.Equipment IS NULL THEN 1#(lf)#(tab)  WHEN h.Job IS NULL AND h.Equipment IS NOT NULL THEN 2#(lf)#(tab)  WHEN h.Job IS NOT NULL AND h.Equipment IS NULL THEN 3#(lf)#(tab)  END AS [PR Hours Category]#(lf),SUM(h.Hours) as Hours #(lf)FROM bPREH as e inner join bPRTH as h on e.Employee = h.Employee and e.PRCo = h.PRCo#(lf)left join bJCCM as m ON  h.PRCo = m.JCCo and h.Job = m.Contract#(lf)left join bJCDM as d ON m.JCCo = d.JCCo and m.Department = d.Department#(lf)left join bPRCC as c ON h.PRCo = c.PRCo and isnull(h.Craft,e.Craft) = c.Craft and isnull(h.Class,e.Class) = c.Class#(lf)left join bPRGR as r ON h.PRCo = r.PRCo and h.PRGroup = r.PRGroup#(lf)left join bPREC as ec ON h.PRCo = ec.PRCo and h.EarnCode = ec.EarnCode #(lf)left join bJCJP as jp ON h.PRCo = jp.JCCo and h.Job = jp.Job and h.Phase = jp.Phase#(lf)WHERE h.EarnCode in (1,2,3,4) #(lf)Group by e.PRCo,e.Employee,h.PREndDate,h.Job,h.EarnCode,isnull(h.Craft,e.Craft),isnull(h.Class,e.Class),e.Craft,e.Class,h.PRGroup,h.Phase,jp.Description,h.Equipment"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompanyKey", type text}})
in
    #"Changed Type";

shared Company = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfINV4rViVYyNwCyg12VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Co = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Co", Int64.Type}, {"Description", type text}})
in
    #"Changed Type";

shared Employee = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint", [Query="SELECT distinct#(lf)cast(e.PRCo as varchar) + ' | ' + cast(e.Employee as varchar) + ' | ' + cast(h.PRGroup as varchar) as  EmployeeKey#(lf),e.Employee#(lf),e.FirstName#(lf),e.LastName#(lf),h.PRGroup#(lf),cast(h.PRGroup as varchar) + ' | ' + r.Description AS [PRGroup Full]#(lf),cast(e.Employee as varchar) + ' | ' + e.FirstName + ' ' +e.LastName AS [Employee Name]#(lf)FROM bPREH as e inner join bPRTH as h on e.Employee = h.Employee and e.PRCo = h.PRCo#(lf)left join bJCCM as m ON  h.PRCo = m.JCCo and h.Job = m.Contract#(lf)left join bPRGR as r ON h.PRCo = r.PRCo and h.PRGroup = r.PRGroup#(lf)--WHERE (h.Job is not null)  and m.ContractStatus = 1"])
in
    Source;

shared Job = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint", [Query="SELECT #(lf)ltrim(rtrim(m.JCCo))+' | '+ltrim(rtrim(m.Contract))  AS ContractKey#(lf),ltrim(rtrim(m.Contract)) + ' | ' + m.Description [Job]#(lf),m.Department+' | '+d.Description as [Division]#(lf),CASE WHEN m.ContractStatus = 1 THEN 'Active'#(lf)      WHEN m.ContractStatus <> 1 THEN 'Closed' END AS [Job Status]#(lf)FROM #(lf)bJCCM as m #(lf)left join bJCDM as d ON m.JCCo = d.JCCo and m.Department = d.Department"])
in
    Source;

shared #"Earn Code" = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint", [Query="SELECT cast(PRCo as varchar) + ' | '+cast(EarnCode as varchar) as ECKey#(lf),cast(EarnCode as varchar) + ' | ' + Description AS EarnCode#(lf)FROM PREC#(lf)WHERE EarnCode IN (1,2,3,4)"])
in
    Source;

shared Trade = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint", [Query="SELECT DISTINCT cast(c.PRCo as varchar) + ' | ' + cast(c.Craft as varchar)+ ' | ' + cast(c.Class as varchar) AS CraftClassKey#(lf),c.PRCo,c.Craft,c.Class,c.Description AS Trade,m.Description AS CraftName#(lf)FROM PRCC c LEFT JOIN PRCM m#(lf)ON c.PRCo = m.PRCo and c.Craft = m.Craft "]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRCo", type text}})
in
    #"Changed Type";

shared #"PR Hours Category" = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKT1JIzEtRcC0szSzITc0rUSjNSyxLzMxJTMpJVYrViVYyAqqCy4JFjCH6lGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PR Hours Category" = _t, #"Category description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PR Hours Category", Int64.Type}, {"Category description", type text}})
in
    #"Changed Type";

shared Equipment = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint"),
    dbo_PBI_dEquipment = Source{[Schema="dbo",Item="PBI_dEquipment"]}[Data]
in
    dbo_PBI_dEquipment;

shared #"Equipment Hours" = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint"),
    dbo_PBI_fEquipment = Source{[Schema="dbo",Item="PBI_fEquipment"]}[Data]
in
    dbo_PBI_fEquipment;

shared Productivity = let
    Source = Sql.Database("sc-exp-sql2\Viewpoint", "Viewpoint"),
    dbo_PBI_fProducitivity = Source{[Schema="dbo",Item="PBI_fProducitivity"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_PBI_fProducitivity,{{"Mth", type date}, {"ActualDate", type date}})
in
    #"Changed Type";