how to write dax measure expression that depends on filters of related tables

[Anonymous]

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.

[dmcguinn]

I have been search for ages to find the correct syntax to apply a dimension table filter to a fact table value during measure calculation...this just saved my weekend!!! Love this community

[MattAllington · Accepted solution]

Edit. Sorry, I just realise the or logic won't work So I deleted my suggestion. I think yours is pretty good

[v-huizhn-msft]

Hi @Anonymous,

Your DAX is good. The filter "WHERE (WOR.Description2 IN ('307','308','302','311','390'))" is for special values, so I am unable to formut it more intelligently after research. We must enumerate the values one by one.

Best Regards,
Angelia