Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 )
Solved! Go to Solution.
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.
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'.
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.
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
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?
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
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.
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
i spotted that and it still is not working???
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
HI Experts Inc Micorsoft team thank you very much. i see the error. much appericated for the excellent feedback as always.
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
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:
Best Regards,
Lin
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |