How to use multiple filters & parameters in a DAX expression for Power BI paginated report

[Anonymous]

I have the following requirement:

Design an .rdl report (multipage) that is hosted in Power BI. The report must have several interactive filters enabled that a user will select to filter out only required information.

I was able to implement one filter with the following DAX expression:

DEFINE VAR __DS0Core = 
    FILTER( SUMMARIZE(
        'User List',
        'User List'[Description],
        'User List'[Email],
        'User List'[FacilityName],
        'User List'[FirstName],
        'User List'[LastName],
        'User List'[SiteName],
        'User List'[Username],
        'User List'[LastLogin]
     ),
        PATHCONTAINS(@Program, 'User List'[Description])
    )

EVALUATE
    __DS0Core

ORDER BY
    'User List'[Description],
    'User List'[Email],
    'User List'[FacilityName],
    'User List'[FirstName],
    'User List'[LastName],
    'User List'[SiteName],
    'User List'[Username],
    'User List'[LastLogin]

Where @Program is a parameter defined on the report

I am stuck now because it seems that PATHCONTAINS doesn't take more than one argument. I need to implement 3 more filters: FacilityName, SiteName, and LastLogin

I just started leaning DAX 2 days ago and seems that my t-SQL experience isn't easily transferable to DAX. Any help would be appreciated.

[Anonymous]

Thank you @Bibiano_Geraldo You sent me on the right path here. How could I not remember to try &&. DAX dimistified 🙂 Here is what actually worked. I had to use PATHCONTAINS for all 4 filters because I use 

=Join(Parameters!Program.Value,"|") for filter values in the report. These are multi-valued filters.

DEFINE VAR __DS0Core = 
    FILTER( SUMMARIZE(
        'User List',
        'User List'[Description],
        'User List'[Email],
        'User List'[FacilityName],
        'User List'[FirstName],
        'User List'[LastName],
        'User List'[SiteName],
        'User List'[Username],
        'User List'[LastLogin]
     ),
   (PATHCONTAINS(@Program, 'User List'[Description])) && 
   (PATHCONTAINS(@FacilityName, 'User List'[FacilityName])) &&
   (PATHCONTAINS(@SiteName, 'User List'[SiteName])) &&
   (PATHCONTAINS(@LastLogin, 'User List'[LastLogin]))
    )

EVALUATE
    __DS0Core

ORDER BY
    'User List'[Description],
    'User List'[Email],
    'User List'[FacilityName],
    'User List'[FirstName],
    'User List'[LastName],
    'User List'[SiteName],
    'User List'[Username],
    'User List'[LastLogin] 

[Bibiano_Geraldo]

@Anonymous, I'm happy it worked, nice job.

[Bibiano_Geraldo · Accepted solution]

Hi @Anonymous ,

If i understood weel, try the bellow DAX:

DEFINE
VAR __DS0Core =
    FILTER (
        SUMMARIZE (
            'User List',
            'User List'[Description],
            'User List'[Email],
            'User List'[FacilityName],
            'User List'[FirstName],
            'User List'[LastName],
            'User List'[SiteName],
            'User List'[Username],
            'User List'[LastLogin]
        ),
        -- Applying multiple filter conditions
        (PATHCONTAINS(@Program, 'User List'[Description]) || NOT ISBLANK(@Program)) &&
        (ISBLANK(@FacilityName) || 'User List'[FacilityName] = @FacilityName) &&
        (ISBLANK(@SiteName) || 'User List'[SiteName] = @SiteName) &&
        (ISBLANK(@LastLogin) || 'User List'[LastLogin] = @LastLogin)
    )

EVALUATE
    __DS0Core

ORDER BY
    'User List'[Description],
    'User List'[Email],
    'User List'[FacilityName],
    'User List'[FirstName],
    'User List'[LastName],
    'User List'[SiteName],
    'User List'[Username],
    'User List'[LastLogin]