Forum Discussion

gooranga1's avatar
gooranga1
Icon for Power Participant rankPower Participant
1 year ago
Solved

DAX multiple text search from SSRS report

I am writing an SSRS report based on DAX. I am not sure if this is possible or not.

I have several parameters for the report that are passed as id's using the below pattern which work fine.

 

 VAR __CompanyPS =
        SUBSTITUTE ( @CompanyDDS, ",", "|" )

 

then in the summarizecolumns filter

 

    FILTER (
        ALL ( Company[Company Id] ),
        PATHCONTAINS ( _CompanyPS, Company[Company Id] )
    )

 

I also want a parameter in the report that allows people to enter sku names or better still parts of sku names to filter as well.

There may be a SKUs called ABC123, ABC456, DEF123. The person could enter "ABC" and that would filter skus with ABC.

I can use ContainRows but that is only on exact matches no string searches as below.

 

DEFINE

   VAR _SkuPS =
        SUBSTITUTE ( "ABC123,ABC456", ",", "|" )

   VAR _SkuTable =
        SELECTCOLUMNS (
            GENERATESERIES ( 1, _len ),
            "Skus", PATHITEM ( _skuPS, [value], TEXT )
        )

EVALUATE
FILTER (
    ALL ( Products[sku], Products[Product Id] ),
    CONTAINSROW ( _skutable, Products[SKU] )
)

 

 it would be really nice to be able to allow multiple string searches as well as actual skus but I can't see how I could do this.

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello gooranga1,

     

    Can you please try this approach to filter with multiple substrings:

    DEFINE
    VAR __CompanyPS = SUBSTITUTE(@CompanyDDS, ",", "|")
    VAR __SkuParam = SUBSTITUTE(@SkuDDS, ",", "|")
    VAR __SkuPatterns = 
        ADDCOLUMNS(
            GENERATESERIES(1, PATHLENGTH(__SkuParam)), 
            "Pattern", PATHITEM(__SkuParam, [Value], TEXT)
        )

    VAR __MatchingSkus = 
        FILTER(
            ALL(Products[SKU]),
            SUMX(
                __SkuPatterns,
                IF(CONTAINSSTRING(Products[SKU], [Pattern]), 1, 0)
            ) > 0
        )

    VAR __FilteredTable =
        CALCULATETABLE(
            SUMMARIZECOLUMNS(
                Company[Company Id],
                Products[SKU],
                Products[Product Id],
                FILTER(
                    ALL(Company[Company Id]),
                    PATHCONTAINS(__CompanyPS, Company[Company Id])
                )
            ),
            __MatchingSkus
        )

EVALUATE __FilteredTable

    Hope this helps.

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    Hello gooranga1,

     

    Can you please try this approach to filter with multiple substrings:

    DEFINE
    VAR __CompanyPS = SUBSTITUTE(@CompanyDDS, ",", "|")
    VAR __SkuParam = SUBSTITUTE(@SkuDDS, ",", "|")
    VAR __SkuPatterns = 
        ADDCOLUMNS(
            GENERATESERIES(1, PATHLENGTH(__SkuParam)), 
            "Pattern", PATHITEM(__SkuParam, [Value], TEXT)
        )

    VAR __MatchingSkus = 
        FILTER(
            ALL(Products[SKU]),
            SUMX(
                __SkuPatterns,
                IF(CONTAINSSTRING(Products[SKU], [Pattern]), 1, 0)
            ) > 0
        )

    VAR __FilteredTable =
        CALCULATETABLE(
            SUMMARIZECOLUMNS(
                Company[Company Id],
                Products[SKU],
                Products[Product Id],
                FILTER(
                    ALL(Company[Company Id]),
                    PATHCONTAINS(__CompanyPS, Company[Company Id])
                )
            ),
            __MatchingSkus
        )

EVALUATE __FilteredTable

    Hope this helps.

  • gooranga1's avatar
    gooranga1
    Icon for Power Participant rankPower Participant
    1 year ago

    thanks Sahir_Maharaj that has worked!

    DAX doesn't seem to be as flexible as SQL in filtering these. It would be optimal to have functionality like;

    SELECT sku 
FROM Products as P
where p.sku like 'ABC%'

    filtering skus that start with 'ABC' only rather than ABC occuring anywhere in the text string. The wildcard characters in DAX don't seem to work in the same way as SQL.

     

    but this solution is definitely good enough for my purposes now.