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.

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.