Forum Discussion

JoshB_'s avatar
JoshB_
Regular Visitor
4 years ago
Solved

For each value in list and multiple conditions

Hi all,

 

I have a data set for customer survey results, and we classify each customer as Good, Passive, Weak. My goal is to create a sankey and track customers who move up or down the classifications by survey period.

 

The goal table I want looks something like this:

Period StartPeriod Start ClassificationPeriod EndPeriod End ClassificationValue
Q2 2020GoodQ4 2020Good 
Q2 2020GoodQ4 2020Passive 
Q2 2020GoodQ4 2020Weak 
Q2 2020PassiveQ4 2020Good 
Q2 2020PassiveQ4 2020Passive 
Q2 2020PassiveQ4 2020Weak 
Q2 2020WeakQ4 2020Good 
Q2 2020WeakQ4 2020Passive 
Q2 2020WeakQ4 2020Weak 
Q4 2020GoodQ2 2021Good 
Q4 2020GoodQ2 2021Passive 
Q4 2020GoodQ2 2021Weak 

 

Which is essentially a matrix of each survey period, the classification then the proceeding period and classification. I have this table set up, the challenge is calculating the value. So the first row is the number of customers who were classified as good in one survey, then good in the proceeding.

 

I'm attempting this with a column calculation. Essentially I want to count from the main survey data table, the number of customers where survey period = period start & classification = Period Start Classification AND survey period = period end & classification = Period end Classification. This of course creates a conflict because you can't filter the table twice.

 

So I'm thinking I want to do something like, for each customer ID in a filtered table (that being the start period and classification) that also appear in another filtered table (end period and classification). Only, I can't seem to get it to work.

 

My customer survey data is like the following:

 

Customer IDSurvey PeriodClassification
100Q2 2020Passive
101Q2 2020Good
100Q4 2020Good
101Q4 2020Good

 

Hope this makes sense. Thank you

Need Help
  • JoshB_'s avatar
    JoshB_
    4 years ago

    Hi everyone, 

     

    I solved it in the end with this:

     

    Var Source_Period = Sankey[Source_Period]
    Var Source_Group = Sankey[Source_Group]
    Var Target_Period = Sankey[Target_Period]
    Var Target_Group = Sankey[Target_Group]

    Var Source_Table = Filter(Customer_Survey_Main, AND(Customer_Survey_Main[Survey_Period] = Source_Period, Customer_Survey_Main[NPS_Group] = Source_Group))
    Var Target_Table = Filter(Customer_Survey_Main, AND(Customer_Survey_Main[Survey_Period] = Target_Period, Customer_Survey_Main[NPS_Group] = Target_Group))

    Return Countrows(Filter(Source_Table, CONTAINS(Target_Table,[CustomerUsername], Customer_Survey_Main[CustomerUsername] )))

    So basically, for each row, I create a virtual table with all the customers who gave a response in that survey, then another table with their response for the next survey. Then count the number of times the same customer appears in both.


    This worked. But it doesn't get updated by slicers and page filters. So will need to work out a way to do it on the survey main table.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    So far so good. The only thing really missing is a lookup of the "prior"  status for each entry.  You can implement that in Power Query or DAX.  Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post (as you did) or use one of the file services. 

  • JoshB_'s avatar
    JoshB_
    Regular Visitor
    4 years ago

    Hi everyone, 

     

    I solved it in the end with this:

     

    Var Source_Period = Sankey[Source_Period]
    Var Source_Group = Sankey[Source_Group]
    Var Target_Period = Sankey[Target_Period]
    Var Target_Group = Sankey[Target_Group]

    Var Source_Table = Filter(Customer_Survey_Main, AND(Customer_Survey_Main[Survey_Period] = Source_Period, Customer_Survey_Main[NPS_Group] = Source_Group))
    Var Target_Table = Filter(Customer_Survey_Main, AND(Customer_Survey_Main[Survey_Period] = Target_Period, Customer_Survey_Main[NPS_Group] = Target_Group))

    Return Countrows(Filter(Source_Table, CONTAINS(Target_Table,[CustomerUsername], Customer_Survey_Main[CustomerUsername] )))

    So basically, for each row, I create a virtual table with all the customers who gave a response in that survey, then another table with their response for the next survey. Then count the number of times the same customer appears in both.


    This worked. But it doesn't get updated by slicers and page filters. So will need to work out a way to do it on the survey main table.