Calculations with Multiple Tables and Multiple RELATED Filters

[AlanBaker]

This probably quite straightforward but I am a new DAX & Power BI User and I have been struggling with the need to create a Measure calculation on a Look up table that references Measures from two different data tables both related back to the Lookup table as many to one.

			Work Orders
			WorkOrder
			123
			456
			789
Plans		Hours
WorkOrder	OperationNo	Plan Hours		WorkOrder	OperationNo	Actual Hours
123	1	2		456	1	4
123	2	3		456	2	3
789	1	12		789	1	9
		Work Order	Plans	Hours	Status
		123	Plan Hrs = 2	Actual Hrs = 0	No Action
		456	Plan Hrs = 0	Actual Hrs = 7	Break-In Work
		789	Plan Hrs = 12	Actual Hrs = 9	Actioned
		100	Plan Hrs = 0	Actual Hrs = 10	Break-In Work
			Desired Result
			Count of WorkOrders
			No Action	1
			Break-In Work	2
			Actioned	1

I want to check if hours were planned in Table Plan and I want to check that Hours were posted in Table Hours and depending on the results I need to determine each Work Order's Status as shown and then Count those results.

I have tried several Measures the latest being and this is to just measure the Break-In Work:

MEASURE: PLANNED_HOURS = SUM(Plans[Plan Hours])        

MEASURE: TIMESHEET_HRS = SUM(Hours[Actual Hours])

WOsNO_BREAK_IN = CALCULATE(

DISTINCTCOUNT(Work Orders [WorkOrder]),

FILTER(Plans, [PLANNED_ HOURS] = 0) && FILTER(Hours, [TIMESHEET_HRS] > 0))

Which gives the error: “A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.”

[CahabaData]

the layout of your data set is helpful, but it is also confusing as to how your actual data is structured which is key for the correct solution.

are Plan Hours and Actual Hours in separate tables - or - do they reside in the same table......  ? 

I will guess separate tables.   Because you need a double join of both Work Order & Operation No, which is not possible in PBI, you would create a calculated column that is a merge of both fields/values.  One does this in the Query Editor.   Then join the 2 tables on this new field.

Once that's going then creating your next steps becomes easier.

But this advice assumes WO+ON is always a unique value.  If there are multiple records with the same WO & ON in the same table then first one is going to need to aggregate them into a single record.

[AlanBaker]

Many thanks for the quick response!

They are indeed separate tables Work Order is the lookup and Hours and Plans are separate tables linked up to it Work Order many to one.

I have tried calculated columns in the Work Order table and I did get the desired results that enabled me to calculate a third column that was equal to either:

Break-In

Actioned

No Action

But the problem is when I utilse the calculated columns in the Power BI Pie Chart the result is NOT influenced by the report filters for say filtering by trade (craft) or department instead I get the result of the complete table which as I understand the training material is what I should expect?

[AlanBaker]

I'm afraid the confusion in the diagram is due to a couple of things I will clarify the Hours Table Header has moved to the left.

The results of the calculations that I require are in the lower half of the spreadsheet.

There is an error in the calculation of Workorder 123 Result which should be 5 not 2

Please see ammended table below:

			Table Work Orders	(One)
			WorkOrder
			123
			456
			789
	Table Plans	(Many)			Table Hours	(Many)
WorkOrder	OperationNo	Plan Hours		WorkOrder	OperationNo	Actual Hours
123	1	2		456	1	4
123	2	3		456	2	3
789	1	12		789	1	9
		RESULTS	SUMMARIES	BELOW
		Work Order	Plans	Hours	Calculated Status
		123	Plan Hrs = 5	Actual Hrs = 0	No Action
		456	Plan Hrs = 0	Actual Hrs = 7	Break-In Work
		789	Plan Hrs = 12	Actual Hrs = 9	Actioned
		100	Plan Hrs = 0	Actual Hrs = 10	Break-In Work
			FINAL Desired Result
			Count of WorkOrders
			No Action	1
			Break-In Work	2
			Actioned	1

[v-ljerr-msft · Accepted solution]

Hi @AlanBaker,

According to your description above, you should be able to use the formulas below to create three measures and show each of them on the Card visual on the report in your scenario. 

WOsNO_BREAK_IN = 
SUMX (
    'Work Orders',
    IF (
        CALCULATE ( SUM ( Plans[Plan Hours] ) ) = 0
            && CALCULATE ( SUM ( Hours[Actual Hours] ) ) > 0,
        1,
        0
    )
)

WOsNO_NO_ACTION = 
SUMX (
    'Work Orders',
    IF (
        CALCULATE ( SUM ( Plans[Plan Hours] ) ) > 0
            && CALCULATE ( SUM ( Hours[Actual Hours] ) ) = 0,
        1,
        0
    )
)

WOsNO_ACTIONED = 
SUMX (
    'Work Orders',
    IF (
        CALCULATE ( SUM ( Plans[Plan Hours] ) ) > 0
            && CALCULATE ( SUM ( Hours[Actual Hours] ) ) > 0,
        1,
        0
    )
)

Regards

[AlanBaker]

Many thanks, a little more knowledge is cemented into place!