For each value in list and multiple conditions

[JoshB_]

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 Start	Period Start Classification	Period End	Period End Classification	Value
Q2 2020	Good	Q4 2020	Good
Q2 2020	Good	Q4 2020	Passive
Q2 2020	Good	Q4 2020	Weak
Q2 2020	Passive	Q4 2020	Good
Q2 2020	Passive	Q4 2020	Passive
Q2 2020	Passive	Q4 2020	Weak
Q2 2020	Weak	Q4 2020	Good
Q2 2020	Weak	Q4 2020	Passive
Q2 2020	Weak	Q4 2020	Weak
Q4 2020	Good	Q2 2021	Good
Q4 2020	Good	Q2 2021	Passive
Q4 2020	Good	Q2 2021	Weak

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 ID	Survey Period	Classification
100	Q2 2020	Passive
101	Q2 2020	Good
100	Q4 2020	Good
101	Q4 2020	Good

Hope this makes sense. Thank you

[JoshB_ · Accepted solution]

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.

[lbendlin]

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.