Countrows with multiple conditions and lookupvalue in DAX

Question

I want to count the amount of contracts where the current contract as well as the previous contract is of type "GREEN".

I have the following tables in my datamodel.

DIM Contract

Contract ID	Preceeding Contract ID	Contract FK
1029	1001	1
1001	null	2

FCT Contract

Contract PK	Object FK
1	ABC
2	DEF
3	GHI
4	JKL

DIM Object

Object PK	Type
ABC	GREEN
DEF	BLUE
GHI	GREEN
JKL	BLUE

So In DIM Contract, I need to go to the row of the preceeding contract, then via Contract FK, via the FCT Contract table, I need to check whether the previous contract type was green, and then I need to check whether the current contract is green as well.

If possible, I want to do a COUNTROW on the FCT Contract table following all these conditions. Preferably I would like to solve this only using measures. But if necessary, I can create custom columns as well. I have no idea where to start. Can anyone help?

Answer 1

You need to use variables to capture the current contract ID and preceding contract ID based on the selected value in the 'DIM Contract' table. Then using the CALCULATE function with the MAX function, you will be able to retrieve the type of the current and preceding contracts from the 'FCT Contract' table by checking if both contract types are "GREEN" and returns 1 if true, and 0 otherwise.

Green Contracts =
VAR CurrentContractID = SELECTEDVALUE('DIM Contract'[Contract ID])
VAR PrecedingContractID = SELECTEDVALUE('DIM Contract'[Preceding Contract ID])
VAR CurrentContractType =
    CALCULATE(
        MAX('DIM Object'[Type]),
        FILTER('FCT Contract', 'FCT Contract'[Contract PK] = CurrentContractID)
    )
VAR PrecedingContractType =
    CALCULATE(
        MAX('DIM Object'[Type]),
        FILTER('FCT Contract', 'FCT Contract'[Contract PK] = PrecedingContractID)
    )
RETURN
    IF(
        CurrentContractType = "GREEN" && PrecedingContractType = "GREEN",
        1,
        0
    )