Filtering across multiple dimensions

[Brill]

Hi Community, 
Currently we conduct customer surveys across multiple times of a customer lifecycle e.g. after joining, 6 months, 12 months, 2 years... etc. In the survey we take some standard measurements in each survey e.g. Customer Satisfaction
In Power BI we can easily filter the survey results to show the total results at each survey time interval. However not all customers will fill out each survey we send. We are looking for a way to add an additional filter to differentiate customers that have completed only the surveys we select to visualise how these customers perceptions have changed over time.  

Here is an example of the surveys a customer might complete:

	Introduction survey	6 month survey	12 month survey	2 year survey
Customer 1	X	X		X
Customer 2	X	X	X
Customer 3	X	X		X
Customer 4	X	X	X	X
Customer 5		X	X

Currently we have a filter (Filter 1) on our dashboard to allow staff to select the results of all customers that completed different surveys e.g. staff might want the results from the Introduction and 12 month surveys. In the above example this would include the results for customer 1 - 4 (introduction) and customers 2, 4 & 5 (12 month survey)

Is there a way to add a secondary filter (Filter 2) to add an additional criteria to show just the results for customers that completed ALL the options selected in Filter 1 e.g. it would just include customers 2 and 4 only as they are the only customers that completed both these surveys?

Any suggestions would be great.

[v-saisrao-msft · Accepted solution]

Hi @Brill

Thank you for reaching out to the Microsoft Fabric Forum Community.

After reviewing the details you provided, I was able to reproduce the scenario, I have used it as sample data on my end and successfully implemented it.   

Dax Measure:  

ValidCustomerSatisfaction = 
VAR SelectedSurveys =
    VALUES('SurveyResults'[SurveyName])
VAR NumSelected =
    COUNTROWS(SelectedSurveys)
RETURN
CALCULATE (
    AVERAGE('SurveyResults'[SatisfactionScore]),
    FILTER (
        'SurveyResults',
        VAR CurrentCustomer = 'SurveyResults'[CustomerID]
        VAR CustomerSurveys =
            CALCULATETABLE (
                VALUES('SurveyResults'[SurveyName]),
                'SurveyResults'[CustomerID] = CurrentCustomer
            )
        RETURN
            COUNTROWS (
                INTERSECT (SelectedSurveys, CustomerSurveys)
            ) = NumSelected
    )
)

Attached the PBIX file for reference.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
 
Thank you

[v-saisrao-msft]

Hi @Brill,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

[v-saisrao-msft]

Hi @Brill,

Checking in to see if your issue has been resolved regarding filtering cross multiple dimensions. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @Brill,

checking in to see if your issue has been resolved. If the response provided was helpful, kindly mark the helpful reply as solution so that others with the same issue can benefit.
let us know if you still need assistance.

Thank you.

[Ashish_Mathur]

Hi,

PBi file attached.

Hope this helps.

[johnt75 · Accepted solution]

You could create a calculation group so that users could choose between showing customers who have completed any of the selected surveys or only those who have completed all the selected surveys.

The calculation item for any of the selected surveys would just use SELECTEDMEASURE(), the item for showing just those customers who have completed all the selected surveys would be

All Selected Surveys = 
VAR NumChosen = COUNTROWS( ALLSELECTED( 'Survey Type'[Survey Type] ) )
VAR CustomersAndNumSurveys = ADDCOLUMNS(
    VALUES( Customer[Customer] ),
    "@num rows", CALCULATE( COUNTROWS( 'Table' ) )
)
VAR ValidCustomers = FILTER( CustomersAndNumSurveys, [@num rows] = NumChosen )
VAR Result = CALCULATE( SELECTEDMEASURE(), ValidCustomers )
RETURN Result

By implementing this in a calculation group it will work for any measure, e.g. counting the number of customers, showing average scores etc.

You can see an example in the attached file.

[Brill]

Thanks. This works well. Will test it out on our dataset. Appreciate it. 

[v-saisrao-msft · Accepted solution]

Hi @Brill

Thank you for reaching out to the Microsoft Fabric Forum Community.

After reviewing the details you provided, I was able to reproduce the scenario, I have used it as sample data on my end and successfully implemented it.   

Dax Measure:  

ValidCustomerSatisfaction = 
VAR SelectedSurveys =
    VALUES('SurveyResults'[SurveyName])
VAR NumSelected =
    COUNTROWS(SelectedSurveys)
RETURN
CALCULATE (
    AVERAGE('SurveyResults'[SatisfactionScore]),
    FILTER (
        'SurveyResults',
        VAR CurrentCustomer = 'SurveyResults'[CustomerID]
        VAR CustomerSurveys =
            CALCULATETABLE (
                VALUES('SurveyResults'[SurveyName]),
                'SurveyResults'[CustomerID] = CurrentCustomer
            )
        RETURN
            COUNTROWS (
                INTERSECT (SelectedSurveys, CustomerSurveys)
            ) = NumSelected
    )
)

Attached the PBIX file for reference.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
 
Thank you

[bhanu_gautam]

@Brill 

Create a measure to count the number of surveys completed by each customer based on the selected surveys in Filter 1.

SelectedSurveyCount =
VAR SelectedSurveys = VALUES('CustomerSurveys'[SurveyType])
RETURN
CALCULATE(
COUNTROWS('CustomerSurveys'),
'CustomerSurveys'[SurveyType] IN SelectedSurveys
)

Create a measure to check if a customer has completed all the surveys selected in Filter 1.

DAX
CompletedAllSelectedSurveys =
VAR SelectedSurveys = VALUES('CustomerSurveys'[SurveyType])
VAR TotalSelectedSurveys = COUNTROWS(SelectedSurveys)
RETURN
IF(
[SelectedSurveyCount] = TotalSelectedSurveys,
1,
0
)

Apply the CompletedAllSelectedSurveys measure as a visual level filter and set it to show only values equal to 1.

Add a slicer for SurveyType to act as Filter 1.
Add a table or any other visual to display the survey results.
In the visual level filters pane for the table, add the CompletedAllSelectedSurveys measure and set the filter to show only where the value is 1.