Number of customers with conditions

[mic_rys]

Hi,

I have problem with measure, please help me Masters! 🙂
Two tables connected one to many.
Customers:

CustomerID	Stan	Type
1	LEAD	Active
2	Sleep	No
3	LEAD	Active

Process:

ProcessID	CustomerID	Type
1	1	Lost
2	2	Progress
3	3	Main
4	3	Main

I need to have number of CustomerID where:

[Customers].Stan = LEAD and
[Customers].Type= Active and
(customer not exist in the process table OR [Process] = Main OR [Process] = Lost)

Thank You a lot!

[Anonymous · Accepted solution]

[# Cust] =
SUMX(
	Customers,
	var TheStan = Customers[Stan]
	var TheType = Customers[Type]
	var Result =
		CALCULATE(
			var DoesntExistInProcess =
				ISEMPTY( Process )
			var HasMainOrLostProcess =
				1 <= COUNTROWS(
					INTERSECT(
						{"lost", "main"},
						DISTINCT( Process[Type] )
					)
				)
			var HasStanLeadAndTypeActive =
				and(
					TheStan = "lead",
					TheType = "active"
				)
			var Condition =
				HasStanLeadAndTypeActive
				&&
				(
					DoesntExistInProcess
					||
					HasMainOrLostProcess
				)
			RETURN
				Condition
		)
	return
		DIVIDE( Result, Result )
)

[mic_rys]

Thank you a lot!