I am trying to write a DAX measure expression to summarize a fact table value based on complex conditions in related dimension tables. The SQL I am trying to emulate looks like this:
SELECT SUM(WOF.EstimatedHours)/100
FROM WorkOrdersFact WO
inner join DimWorkOrderOverrides WOR on WO.DimWorkOrderOverridesID = WOR.DimWorkOrderOverridesID
inner join DimWorkOrderDocType WDT on WO.DimWorkOrderDocTypeID = WDT.DimWorkOrderDocTypeID
WHERE (WOR.Description2 IN ('307','308','302','311','390')) OR WDT.WorkOrderDocType = 'SV' and WO.WorkOrderType = 'X'
The following code represents the best I could come up with but it seems to me that there must be a better way. I am applying the OR conditions by creating two separate CALCULATE functions then adding the results before dividing by 100.
(
CALCULATE(SUM('Work Orders'[Estimated Hours]),
FILTER('Work Order Doc Type', 'Work Order Doc Type'[Work Order Doc Type] = "SV"),
FILTER('Work Orders', 'Work Orders'[Work Order Type] = "X"))
+
CALCULATE(SUM('Work Orders'[Estimated Hours]),
FILTER('Work Order Overrides', 'Work Order Overrides'[Work Order GL Description2] in {"307","308","302","311","390"}))
)
/100
note that the "IN" operator is now allowed but is not recognized by intellisense. Also note that the referenced tables are already joined in the PBI Relationships view.
Please give me your ideas on the optimal way to accomplish this.
