Forum Discussion
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 classificati...
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.
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.
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.