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 Customer 2018 2019 AA01 Satisfied Dissatisfied BB01 Dissatisfied Satisfied CC01 Satisfied Satisfied   Both 2018 and 2019 ...
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

    • sglee's avatar
      sglee
      Frequent Visitor
      6 years ago

      az38 

       

      The Syntax is incorrect