Forum Discussion

Brill's avatar
Brill
New Member
1 year ago
Solved

Filtering across multiple dimensions

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 measur...
  • v-saisrao-msft's avatar
    1 year ago

    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

  • johnt75's avatar
    1 year ago

    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.