Forum Discussion

richrich123's avatar
richrich123
Helper III
6 years ago
Solved

Filtering out 'non responses'

Hi all,   Im struggling to find a way to see a list of customers who havent submitted any survey responses. Im sure there must be a way but Im struggling to find one that works.   I send out a su...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Yes.  [Score] is correct (my bad).  Also, since that must be a numeric column, the "" isn't working.  You can replace that part with >0 (or >-1, if you expect 0 Scores in the data) in place of the <>"".

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

You could try a measure like this, which would work on a table visual with a Client Name column (w/ or w/o ContactName):

 

Company Needs Follow Up = var currentclient = selectedvalue(Table[ClientName])

var clientresponsecount = calculate(countrows(Table), all(Table), Table[ClientName]=currentclient, Table[Description]="")+0

return if(clientresponsecount = 0, "Y", "N")

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • richrich123's avatar
    richrich123
    Helper III
    6 years ago

    Hi mahoneypat ,

     

    Many thanks for your help. I tried this and it isnt right for me, I think due to the relationships. I dont have the account name and identifier for a response (score) in the same table. 

     

    In the Dynamics npssurveyresults entity, everyone who has been sent a survey has a new record created and the score only has a value if its been returned so if score = blank, no survey returned

     

    Is there a way to do this by cross referencing the measure between accounts to look at client names in the accounts table and if they have returned a response [score]  from the npssurveyresults table?

     

    Thanks

     

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      6 years ago

      Thanks for providing the diagram.  Since there is a 1:1 between Contacts and surveys, you could try this measure:

       

      Needs Follow Up = var countofresponses= calculate(countrows(mercury_npssurveyresults), mercury_nspsurveyresults[Response]<>"")
      return if(isblank(countofresponses), "Y", "N")

       

      This assumes you'll have a Table visual with the Name column from the Accounts table.

       

      If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

      Regards,

      Pat

      • richrich123's avatar
        richrich123
        Helper III
        6 years ago

        Hi mahoneypat ,

         

        Thanks for the revised suggestion. I don't have a field called response. I have one called Score which is either null if no response or has the given numner (1-10) if they have responded. Assuming that was the one you meant?

         

        I have swapped response for score and tested but get an error

         

        Error Message:
        MdxScript(Model) (4, 115) Calculation error in measure 'mercury_npssurveyresults'[Measure 2]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

         

        Thanks

        Rich