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.   VA...
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.

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.