cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Route217 Member
Member

Cannot get my DAX Measure to filter the destination file

Hi Experts

 

I have the following DAX whichis supposed to filter the desintation pbix file based on the filter criteria selected by the user. When i click on the URL link below, nothing happens in the destination file, it just load and the filters are not getting applied. i am trying to use a measure as a hyperlink...

 

I cannot see whats i have done wrong been trying to trouble shoot this for a few hours and keep on hitting a wall.

Report Filter = 
    VAR oL = "DIM Sales Person/Office Location eq '" & SELECTEDVALUE( 'DIM Sales Person'[Office Location] ) & "'" 
    VAR oC = "DIM Sales Person/City eq '" & SELECTEDVALUE('DIM Sales Person'[City]) & "'" 
    VAR oS  = "DIM Shipper/Ship Company Name eq '" & SELECTEDVALUE('DIM Shipper'[Ship Company Name]) & "'"
    VAR oP = "DIM Product/Category Name eq '" & SELECTEDVALUE('DIM Product'[Category Name]) & "'"
    VAR oSP = "DIM Sales Person/Sales Person eq '" & SELECTEDVALUE('DIM Sales Person'[Sales Person]) & "'"
    VAR oRegion = "DIM Sales Person/Sales Region eq '" & SELECTEDVALUE('DIM Sales Person'[Sales Region]) & "'"
    VAR oDate = IF(ISFILTERED( 'Calendar'[Date] ), "Calendar/Date le"  & MAX('Calendar'[Date]) & "and Calendar/Date gt" & MIN('Calendar'[Date]))
    RETURN

    [Report URL] & 
    SWITCH(
        TRUE,
        ISFILTERED( 'DIM Sales Person'[Office Location] ) &&  ISFILTERED( 'DIM Sales Person'[City]  ), "?filter=" & oL & " and " & oC & " and " & oS & " and " & oP & " and " & oSP & " and " & oRegion & " and " & oDate,
        ISFILTERED( 'DIM Sales Person'[Office Location] ), "?filter=" & oL, 
        ISFILTERED( 'DIM Sales Person'[City]  ), "?filter=" & oC,
        ISFILTERED( 'DIM Shipper'[Ship Company Name]  ), "?filter=" & oS,
        ISFILTERED( 'DIM Product'[Category Name] ), "?filter=" & oP,
        ISFILTERED( 'DIM Sales Person'[Sales Person]), "?filter=" & oSP,
        ISFILTERED( 'DIM Sales Person'[Sales Region]), "?filter=" & oRegion, 
        oDate
        
        )

PS happy to post a link to data if required.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cannot get my DAX Measure to filter the destination file

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/af3aec6d-bf0a-4630-b357-...DIM Sales Person/Office Location eq 'USA' and DIM Sales Person/City eq '<Removed>' and DIM Shipper/Ship Company Name eq '<Removed>' and DIM Product/Category Name eq '<Removed>' and DIM Sales Person/Sales Person eq '<Removed>' and DIM Sales Person/Sales Region eq '<Removed>' and Calendar/Date le12/31/2017and Calendar/Date gt11/7/2014

 

In the above, DIM Sales Person and others need to have spaces replaced with _x0020_

 

 

9 REPLIES 9
Super User
Super User

Re: Cannot get my DAX Measure to filter the destination file

@Route217  - Please share a link to pbix. 

Route217 Member
Member

Re: Cannot get my DAX Measure to filter the destination file

https://www.dropbox.com/s/8el1g6z3oreyy0q/URL_Link_Test_File_6.pbix?dl=0

 

try and publish "Duplicate of Company Details"  as a new pbix file and you'll need to update the Report URL.

I am using measure Report Filter 1 as my hyperlink to destination published pbix.

 

you could also use Report Filter 2 both should work agin same issue with this DAX code too.

Super User
Super User

Re: Cannot get my DAX Measure to filter the destination file

In Report Filter, some spaces need to be encoded.

Report Filter 2 doesn't contain the ?filter=

Both have some instances where there is not a space before the word "and"

Super User
Super User

Re: Cannot get my DAX Measure to filter the destination file

For troubleshooting, I'd recommend trying one filter at a time and then combine them.

Route217 Member
Member

Re: Cannot get my DAX Measure to filter the destination file

hi

 

can you point one out for me please in Report Filter

 

Route217 Member
Member

Re: Cannot get my DAX Measure to filter the destination file

hi i have just tried

 

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/af3aec6d-bf0a-4630-b357-4e057c9c4bd2/ReportSectiond77d965cc24737ab52b1?filter=DIMSalesPerson/OfficeLocation eq 'USA'

for one filter and no result.

Super User
Super User

Re: Cannot get my DAX Measure to filter the destination file

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/af3aec6d-bf0a-4630-b357-...DIM Sales Person/Office Location eq 'USA' and DIM Sales Person/City eq '<Removed>' and DIM Shipper/Ship Company Name eq '<Removed>' and DIM Product/Category Name eq '<Removed>' and DIM Sales Person/Sales Person eq '<Removed>' and DIM Sales Person/Sales Region eq '<Removed>' and Calendar/Date le12/31/2017and Calendar/Date gt11/7/2014

 

In the above, DIM Sales Person and others need to have spaces replaced with _x0020_

 

 

Super User
Super User

Re: Cannot get my DAX Measure to filter the destination file

Instead of removing spaces from Table / Column, you need to replace spaces with _x0020_

Route217 Member
Member

Re: Cannot get my DAX Measure to filter the destination file

Thanks for the excellent feedback. Much appreciated I solved the solution using just that. Once again thank u.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 178 members 2,348 guests
Please welcome our newest community members: