Forum Discussion
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
)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.
21 Replies
hi, Anonymous
If change the format of "oDate" and then try it again.
and could you share your sample pbix file?
Best Regards,
Lin
Hi Lin (ps many thanks for the feedback and assistance)
pbix file with sample data...
https://www.dropbox.com/s/l504k9cxsqthyvx/URL_Link_Test_File_7.pbix?dl=0
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:
Best Regards,
Lin
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.
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.
Anonymous
Have you used quotes as in the example Docs provided?
Table/Date gt '2018-08-03'.
