Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Issue with dates filters

Hi Expert

 

I have managed to write most the DAX for the URL filtering and all of the formula is working fine up until the point where the data filter oDAte kicks in. then nothing happens when i click the hyperlink URL in the dashboard the hyperlink goes to PBI Services but it removes all the filters.

 

not sure what i am doing wrong or have done wrong.

 

Report Filter = 
    VAR oL = "DIMSalesPerson/OfficeLocation eq '" & SELECTEDVALUE('DIMSalesPerson'[OfficeLocation]) & "'" 
    VAR oC = "DIMSalesPerson/City eq '" & SELECTEDVALUE('DIMSalesPerson'[City]) & "'" 
    VAR oS  = "DIMShipper/ShipCompanyName eq '" & SELECTEDVALUE('DIMShipper'[ShipCompanyName]) & "'"
    VAR oP = "DIMProduct/CategoryName eq '" & SELECTEDVALUE('DIMProduct'[CategoryName]) & "'"
    VAR oSP = "DIMSalesPerson/SalesPerson eq '" & SELECTEDVALUE('DIMSalesPerson'[SalesPerson]) & "'"
    VAR oRegion = "DIMSalesPerson/SalesRegion eq '" & SELECTEDVALUE('DIMSalesPerson'[SalesRegion]) & "'"
    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('DIMSalesPerson'[OfficeLocation]) &&  ISFILTERED('DIMSalesPerson'[City]), "?filter=" & oL & " and " & oC & " and " & oS & " and " & oP & " and " & oSP & " and " & oRegion & " and " & oDate,
            ISFILTERED('DIMSalesPerson'[OfficeLocation]), "?filter=" & oL, 
            ISFILTERED('DIMSalesPerson'[City]), "?filter=" & oC,
            ISFILTERED('DIMShipper'[ShipCompanyName]), "?filter=" & oS,
            ISFILTERED('DIMProduct'[CategoryName]), "?filter=" & oP,
            ISFILTERED('DIMSalesPerson'[SalesPerson]), "?filter=" & oSP,
            ISFILTERED('DIMSalesPerson'[SalesRegion]), "?filter=" & oRegion, 
            oDate
        
        )
1 ACCEPTED SOLUTION
Sergiy
Resolver II
Resolver II

Hi @Anonymous ,

 

Your measure generates the following link:

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/69ce2f6f-8fd7-4b5f-a758-98cbbb0064b6/ReportSectiond77d965cc24737ab52b1?filter=DIMSalesPerson/OfficeLocation eq 'USA' and DIMSalesPerson/City eq 'Seattle' and DIMShipper/ShipCompanyName eq 'United Package' and DIMProduct/CategoryName eq 'Dairy Products' and DIMSalesPerson/SalesPerson eq 'Davolio, Nancy' and DIMSalesPerson/SalesRegion eq 'WA' and Calendar/Date le 5/28/2017 and Calendar/Date ge 5/20/2015

 

According to Docs you are expected to use 'YYYY-MM-DD' or 'YYYY-MM-DDT00:00:00' date format:

https://docs.microsoft.com/en-us/power-bi/service-url-filters#date-data-types

 

I'd try changing date format.

View solution in original post

21 REPLIES 21
Sergiy
Resolver II
Resolver II

Hi @Anonymous ,

 

Your measure generates the following link:

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/69ce2f6f-8fd7-4b5f-a758-98cbbb0064b6/ReportSectiond77d965cc24737ab52b1?filter=DIMSalesPerson/OfficeLocation eq 'USA' and DIMSalesPerson/City eq 'Seattle' and DIMShipper/ShipCompanyName eq 'United Package' and DIMProduct/CategoryName eq 'Dairy Products' and DIMSalesPerson/SalesPerson eq 'Davolio, Nancy' and DIMSalesPerson/SalesRegion eq 'WA' and Calendar/Date le 5/28/2017 and Calendar/Date ge 5/20/2015

 

According to Docs you are expected to use 'YYYY-MM-DD' or 'YYYY-MM-DDT00:00:00' date format:

https://docs.microsoft.com/en-us/power-bi/service-url-filters#date-data-types

 

I'd try changing date format.

Anonymous
Not applicable

Sergiy and Lin

 

I have chnaged the date time format as you have shown in the question. but i am still getting the following error when clicking the hyperlink. The hyperlink goes to power BI services but does not filter the report as there still appears to be a date error. i have changed the ate format in Power Query accordingly.

Capture.PNG

 

@Anonymous 

Have you used quotes as in the example Docs provided?

Table/Date gt '2018-08-03'.

 

Anonymous
Not applicable

Hi Sergiy

 

VAR oDate = if(ISFILTERED('Calendar'[Date]), "Time/Date le "  & FORMAT(MAX('Calendar'[Date]),"YYYY-MM-DD") & " and Time/Date ge " & FORMAT(MIN('Calendar'[Date]),"YYYY-MM-DD"))

how would you add quotes here, unless i am beng silly. I changed teh date format accordingly, but how do you get the quotes??

thannks.. 

i can see from my web URL there are no quotes on the date parameter.

1.png

Anonymous
Not applicable

any luck???

I did some tests.

I was wrong about using quotes. The Docs misled me.

 

The following links work as expected:

.........?filter=Projects/Date lt 2019-04-04

.........?filter=Projects~2FDate%20lt%202019-04-04

 

I tested links in a browser window

Anonymous
Not applicable

Sergiy

 

Based on my test file how would you change the date parameters?

go to the browser, loging to Power BI and paste into the address string the following line then press Enter

 

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/69ce2f6f-8fd7-4b5f-a758-... le 2017-12-31 and Time/Date ge 2015-10-18

 

Will the report be filtered?

 

 

Anonymous
Not applicable

sergiy

 

just typed the following in ad the report did not filter..

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/69ce2f6f-8fd7-4b5f-a758-98cbbb0064b6/ReportSectiond77d965cc24737ab52b1?filter= Time/Date le 2017-12-31 and Time/Date ge 2014-01-23

you've entered one extra space symbol. Use this one:

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/69ce2f6f-8fd7-4b5f-a758-98cbbb0064b6/ReportSectiond77d965cc24737ab52b1?filter=Time/Date le 2017-12-31 and Time/Date ge 2015-10-18

what is about this one? 🙂

 

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/69ce2f6f-8fd7-4b5f-a758-98cbbb0064b6/ReportSectiond77d965cc24737ab52b1?filter=Calendar/Date le 2017-12-31 and Calendar/Date ge 2015-10-18
Anonymous
Not applicable

message is "filter comes for query string parameter that was added to report URL"

you haven't got Time table in your! Model

You should change Time to Calendar.

 

1.png

 

https://app.powerbi.com/groups/6a4fa620-97e6-425b-ba79-1c9b9c209e68/reports/69ce2f6f-8fd7-4b5f-a758-98cbbb0064b6/ReportSectiond77d965cc24737ab52b1?filter=Calendar/Date le 2017-12-31 and Calendar/Date ge 2015-10-18

 

 

Anonymous
Not applicable

i spotted that and it still is not working???

 

Anonymous
Not applicable

hi Sergiy

 

Just tried that and not working... ill up load a pbix file.

 

https://www.dropbox.com/s/e05l96vizdauusn/URL_Link_Test_File_7.pbix?dl=0

 

 

 

Anonymous
Not applicable

HI Experts Inc Micorsoft team thank you very much. i see the error. much appericated for the excellent feedback as always.

v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

If change the format of "oDate" and then try it again.

and could you share your sample pbix file?

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

hi lin

 

did you get the pbix file??

HI, @Anonymous 

The problem is the format of your date in the formula. Just do some adjust as below:

Report Filter = 
    VAR oL = "DIMSalesPerson/OfficeLocation eq '" & SELECTEDVALUE('DIMSalesPerson'[OfficeLocation]) & "'" 
    VAR oC = "DIMSalesPerson/City eq '" & SELECTEDVALUE('DIMSalesPerson'[City]) & "'" 
    VAR oS  = "DIMShipper/ShipCompanyName eq '" & SELECTEDVALUE('DIMShipper'[ShipCompanyName]) & "'"
    VAR oP = "DIMProduct/CategoryName eq '" & SELECTEDVALUE('DIMProduct'[CategoryName]) & "'"
    VAR oSP = "DIMSalesPerson/SalesPerson eq '" & SELECTEDVALUE('DIMSalesPerson'[SalesPerson]) & "'"
    VAR oRegion = "DIMSalesPerson/SalesRegion eq '" & SELECTEDVALUE('DIMSalesPerson'[SalesRegion]) & "'"
    VAR oDate = if(ISFILTERED('Calendar'[Date]), "Time/Date le "  & FORMAT(MAX('Date'[Date]),"YYYY-MM-DD") & " and Time/Date ge " & FORMAT(MIN('Date'[Date]),"YYYY-MM-DD"))
    VAR oDate = if(ISFILTERED('Calendar'[Date]), "Calendar/Date le "  & MAX('Calendar'[Date]) & " and Calendar/Date ge " & MIN('Calendar'[Date]))
    RETURN

    [Report URL] & 
    SWITCH(
        TRUE, 
            ISFILTERED('DIMSalesPerson'[OfficeLocation]) && ISFILTERED('DIMSalesPerson'[City]), "?filter=" & oL & " and " & oC & " and " & oS & " and " & oP & " and " & oSP & " and " & oRegion & " and " & oDate,
            ISFILTERED('DIMSalesPerson'[OfficeLocation]), "?filter=" & oL, 
            ISFILTERED('DIMSalesPerson'[City]), "?filter=" & oC,
            ISFILTERED('DIMShipper'[ShipCompanyName]), "?filter=" & oS,
            ISFILTERED('DIMProduct'[CategoryName]), "?filter=" & oP,
            ISFILTERED('DIMSalesPerson'[SalesPerson]), "?filter=" & oSP,
            ISFILTERED('DIMSalesPerson'[SalesRegion]), "?filter=" & oRegion,
            oDate
        
        )

and here is a similar post I had solved for you refer to:

https://community.powerbi.com/t5/Service/Sharing-a-Pre-Filtered-Report-using-URL-Dates-Between/m-p/693841#M68222

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.