Count based on criteria from 2 different table

[sglee]

I want to analyze trend of change from year to year

Customer	2018	2019
AA01	Satisfied	Dissatisfied
BB01	Dissatisfied	Satisfied
CC01	Satisfied	Satisfied

Both 2018 and 2019 are 2 different table.

The satisfaction is based on formula below

Satisfaction = IF([CSI]>=4, "Satisfied", "Dissatisfied")

I want to know how many from satisfied to dissatisfied and how many from dissatisfied to satisfied between 2018 & 2019

What formula can I use to analyze the trend?

Thank you.

[az38]

Hi @sglee 

First, obviously you need relationships between 2018, 2019 and customer tables.

I guess, the Customer field should be the key for these relationships

If Im right you could create a columns in the CustomerTable like

ColumnSatToDis = CALCULATE(Countrows('CustomerTable'), 
FILTER(ALL('CustomerTable'), 
RELATED('Table2018'[Satisfaction])="Satisfied" && RELATED('Table2019'[Satisfaction])="Dissatisfied"
)
)

and

ColumnDisToSat = CALCULATE(Countrows('CustomerTable'), 
FILTER(ALL('CustomerTable'), 
RELATED('Table2019'[Satisfaction])="Satisfied" && RELATED('Table2018'[Satisfaction])="Dissatisfied"
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[sglee]

Hi @az38 

Yes, table 2018 & 2019 relationship is built by Customer Code in both table.

However, I unable to set up the below

RELATED('Table2018'[Satisfaction])="Satisfied" && RELATED('Table2019'[Satisfaction])="Dissatisfied"

[az38]

Hi @sglee 

what kind of error do you get?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[sglee]

@az38 

The Syntax is incorrect