locked
Is there a DAX function that will search within a field? RRS feed

  • Question

  • We are looking for a solution to the problem of how we can look up our users based on their capabilities. The current solution has a column in the table for each capability (Capabillity_1, Capability_2 etc) and a disconnected table that contains a unique list of the possible capabilities.

    At the moment I have a DAX function working that uses a slicer to look up unique capabilities across values stored in 5 columns.

    IF(HASONEVALUE(Data[Capability_1]) &&
       HASONEVALUE(Data[Capability_2]) &&
       HASONEVALUE(Data[Capability_3]) &&
       HASONEVALUE(Data[Capability_4]) &&
       HASONEVALUE(Data[Capability_5]),
     IF(
        CONTAINS(
            VALUES(Choices[Capabilities]),
            Choices[Capabilities],
            VALUES(Data[Capability_1])
        )  ||
        CONTAINS(
            VALUES(Choices[Capabilities]),
            Choices[Capabilities],
            VALUES(Data[Capability_2])
        )  ||
        CONTAINS(
            VALUES(Choices[Capabilities]),
            Choices[Capabilities],
            VALUES(Data[Capability_3])
        )  ||
        CONTAINS(
            VALUES(Choices[Capabilities]),
            Choices[Capabilities],
            VALUES(Data[Capability_4])
        )  ||
        CONTAINS(
            VALUES(Choices[Capabilities]),
            Choices[Capabilities],
            VALUES(Data[Capability_5])
       ),
        1,
        BLANK()
     ),
    BLANK()
    )

    The obvious disadvantage to this is that as people add in more capabilities we have to add more columns, and some joker has just added in over 100 capabilities!

    What I would like is to find a way of looking up the unique values in column that has a users capabilities comma separated, is this possible?

    In the diagram above the red line indicates what I have now (and is working) and the black line indicates what I would like.

    In the red section Column 1 is my 'unique list' table and capability_1 to _5 are the lists of values.

    In the black section Column 1 is my 'unique list' table and capability_1 is the comma separated column of capabilities

    Please feel free to ask if anything isn't clear, or even the solution needs to be addressed further upstream - at the database level.

    Thanks

    Paul

    Tuesday, November 11, 2014 4:24 PM

Answers

  • Hi Paul,

    This is quite an interesting problem and hopefully the following will help. I have created two Linked Tables that I've derived from your screen shots:

    A table called 'DelimitedCapabilities':

    Capabilities
    Building structure-3 Foundations and geotechnics-3
    Facades-1,Water network-4
    Building   structure-4,Wastewater treatment-4
    Facades-1,Water network-4
    Wastewater treatment-3,Water network-4
    Wastewater treatment-4
    Building structure-1,Wastewater treatment-4
    Building structure-1,Facades-2,Dams-4
    Acoustics-2
    Building structure-1,Facades-2,Dams-4

    A table called 'CapabilityLookup'

    Capability
    Acoustics-2
    Building structure-1
    Building structure-3
    Building structure-4
    Dams-4
    Facades-1
    Facades-2
    Fire engineering-2
    Foundations and geotechnics-3
    Water network-4
    Wastewater treatment-3
    Wastewater treatment-4
    Water network-4
    Water resources-3
    Wastewater treatment-3

    Having loaded these both into the Data Model, I've created a Calculated Field using the following DAX formula - It's one possible implementation of a dynamic value search within a delimited field:

    Capability Existence Check:=IF(
      HASONEVALUE(DelimitedCapabilities[Capabilities]) 
      && HASONEVALUE(CapabilityLookup[Capability]), 
      PATHCONTAINS(
        SUBSTITUTE(
          VALUES(DelimitedCapabilities[Capabilities]),
          ",",
          "|"
        ), 
        VALUES(CapabilityLookup[Capability])
      )
    ) + 0


    We're using the SUBSTITUTE function to convert the delimited text into something we can more easily manipulate with the DAX PATH related functions by replacing the commas with pipe. We're then using the PATHCONTAINS function to check whether the currently selected value in the CapabilityLookup[Capability] column is present in the value returned by the DelimtedCapabilities[Capabilities] column. The end result is a Calculated Field value that can be filtered by in a Pivot Table to only return values from the DelimtedCapabilities[Capabilities] column that contain the value selected in the CapabilityLookup[Capability] column. I added a 0 to the end result so that the TRUE/FALSE value will be implicitly converted to an integer and give a 1 for TRUE and a 0 for FALSE. This allowed for easy filtering on the returned value in the Pivot Table using the 'greater than' filtering option. I've included screenshots of the outcomes.

    A simple Pivot Table with the 'Capabilities' column on rows, 'Capability' column as a Slicer, and the 'Capability Existence Check' Calculated Field in values:

    The Pivot Table after filtering the Capability [Existence Check Measure]:

    I supposed the Calculated Field column could be hidden in the Pivot Table to keep things clean. I haven't tested this solution for performance, and it may not be a final solution for you but perhaps it will help you to think along similar lines or even come up with a more refined approach.

    Hope this helps.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Edited by Michael Amadi Tuesday, November 11, 2014 9:36 PM Small edits
    • Marked as answer by Paul Trower Wednesday, November 12, 2014 9:20 AM
    Tuesday, November 11, 2014 8:28 PM

All replies

  • Hi Paul,

    This is quite an interesting problem and hopefully the following will help. I have created two Linked Tables that I've derived from your screen shots:

    A table called 'DelimitedCapabilities':

    Capabilities
    Building structure-3 Foundations and geotechnics-3
    Facades-1,Water network-4
    Building   structure-4,Wastewater treatment-4
    Facades-1,Water network-4
    Wastewater treatment-3,Water network-4
    Wastewater treatment-4
    Building structure-1,Wastewater treatment-4
    Building structure-1,Facades-2,Dams-4
    Acoustics-2
    Building structure-1,Facades-2,Dams-4

    A table called 'CapabilityLookup'

    Capability
    Acoustics-2
    Building structure-1
    Building structure-3
    Building structure-4
    Dams-4
    Facades-1
    Facades-2
    Fire engineering-2
    Foundations and geotechnics-3
    Water network-4
    Wastewater treatment-3
    Wastewater treatment-4
    Water network-4
    Water resources-3
    Wastewater treatment-3

    Having loaded these both into the Data Model, I've created a Calculated Field using the following DAX formula - It's one possible implementation of a dynamic value search within a delimited field:

    Capability Existence Check:=IF(
      HASONEVALUE(DelimitedCapabilities[Capabilities]) 
      && HASONEVALUE(CapabilityLookup[Capability]), 
      PATHCONTAINS(
        SUBSTITUTE(
          VALUES(DelimitedCapabilities[Capabilities]),
          ",",
          "|"
        ), 
        VALUES(CapabilityLookup[Capability])
      )
    ) + 0


    We're using the SUBSTITUTE function to convert the delimited text into something we can more easily manipulate with the DAX PATH related functions by replacing the commas with pipe. We're then using the PATHCONTAINS function to check whether the currently selected value in the CapabilityLookup[Capability] column is present in the value returned by the DelimtedCapabilities[Capabilities] column. The end result is a Calculated Field value that can be filtered by in a Pivot Table to only return values from the DelimtedCapabilities[Capabilities] column that contain the value selected in the CapabilityLookup[Capability] column. I added a 0 to the end result so that the TRUE/FALSE value will be implicitly converted to an integer and give a 1 for TRUE and a 0 for FALSE. This allowed for easy filtering on the returned value in the Pivot Table using the 'greater than' filtering option. I've included screenshots of the outcomes.

    A simple Pivot Table with the 'Capabilities' column on rows, 'Capability' column as a Slicer, and the 'Capability Existence Check' Calculated Field in values:

    The Pivot Table after filtering the Capability [Existence Check Measure]:

    I supposed the Calculated Field column could be hidden in the Pivot Table to keep things clean. I haven't tested this solution for performance, and it may not be a final solution for you but perhaps it will help you to think along similar lines or even come up with a more refined approach.

    Hope this helps.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Edited by Michael Amadi Tuesday, November 11, 2014 9:36 PM Small edits
    • Marked as answer by Paul Trower Wednesday, November 12, 2014 9:20 AM
    Tuesday, November 11, 2014 8:28 PM
  • Thanks for your posting - that seems to work OK. I also stumbled across another solution:

    =If ( Hasonevalue(Data[Capability_1] ),

              Calculate (

                LASTNONBLANK (Choices[Capabilities], 1 ),

                Filter ( Choices, SEARCH(Choices[Capabilities],Values(Data[Capability_1]),1,0) > 0 )

              )

          )

    I am going to test them both and see which one is most performant.

    Thanks

    Paul

    Wednesday, November 12, 2014 9:09 AM