Forum Discussion

sglee's avatar
sglee
Frequent Visitor
6 years ago

Count based on criteria from 2 different table

I want to analyze trend of change from year to year

Customer20182019
AA01SatisfiedDissatisfied
BB01DissatisfiedSatisfied
CC01SatisfiedSatisfied

 

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.

4 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      sglee
      Frequent Visitor
      6 years ago

      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's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi sglee 

        what kind of error do you get?

         

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