topic Count based on criteria from 2 different table in DAX Commands and Tips

Count based on criteria from 2 different table

sglee — Thu, 16 Jan 2020 09:46:34 GMT

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.

Re: Count based on criteria from 2 different table

az38 — Fri, 17 Jan 2020 10:38:25 GMT

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

Re: Count based on criteria from 2 different table

sglee — Mon, 20 Jan 2020 08:39:01 GMT

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"

Re: Count based on criteria from 2 different table

az38 — Mon, 20 Jan 2020 09:41:44 GMT

Hi sglee

what kind of error do you get?

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

Re: Count based on criteria from 2 different table

sglee — Tue, 21 Jan 2020 01:49:37 GMT

az38

The Syntax is incorrect