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!

Dynamic filtering date hierarchy range with Power BI & Analysis Services

I can do this for a single value but no luck so far for multiple date values from date hierarchy slicer/filter.

Please help.
Status: New
Comments
v-qiuyu-msft
Community Support

Hi @Anonymous, 

 

You can change slicer mode to Between, then you can set desired date range. See: https://docs.microsoft.com/en-us/power-bi/desktop-slicer-numeric-range

 

q1.PNG

 

Best Regards,
Qiuyun Yu

Anonymous
Not applicable

Hi @v-qiuyu-msft,
Actually you are talking about something else, let me rephrase may be I wasn't clear enough earlier.

I need to find a solution to use dynamic filtering for a date hierarchy range between reports.

 

I checked Patrick’s demos here and I was able to get the url constructed as desired inside Power BI itself but since we are using Live Connection to SSAS I can’t change the data category of the URL measure to WebURL hence it doesn’t get parsed correctly when clicked from the report.

My understanding is that this needs to be done on the cube side by adding a new column in our Tabular model but this only works for a single string value which I tried and was successful (marked in green below), but how can I do it for a date range??


PFA samples from the DAX that I created and played around with.

StudentsFilterDateURL =
var rpturl = "https://app.powerbi.com/groups/95355d35-5036-41e7-b890-393d5daf3669/reports/fdcc306c-962d-4bca-aae6-..."

var rptfilterdate = "DimDate/Date in ('" & CONCATENATEX(VALUES(DimDate[Date]),DimDate[Date], "', '") & "')"

return

rpturl&rptfilterdate



StudentsFilterURL =
var rpturl = "https://app.powerbi.com/groups/95355d35-5036-41e7-b890-393d5daf3669/reports/fdcc306c-962d-4bca-aae6-..."

var rptfilterbsu = "BusinessUnit/bsu_full_name eq '" & SELECTEDVALUE(BusinessUnit[bsu_full_name]) & "'"
var rptfilterdate = " and DimDate/Date in ('" & CONCATENATEX(VALUES(DimDate[Date]),DimDate[Date], "', '") & "')"

return

rpturl&rptfilterbsu&rptfilterdate

Thanks,

Aya

v-qiuyu-msft
Community Support

Hi @Anonymous, 

 

To use IN operator to filter report in report URL, the values to the right of in must be a comma-separated list enclosed in parentheses. See: https://docs.microsoft.com/en-us/power-bi/service-url-filters#operators

 

In your scenario, please try to modify the red section to DimDate/Date in ('" & CONCATENATEX(VALUES(DimDate[Date]),DimDate[Date], ', ') & "') then test again. Instead add this formula in SSAS model directly,I would suggest you import SSAS related dimension or measures to Power BI desktop then create a measure/calculated column to test the DAX. 

 

Best Regards,
Qiuyun Yu 

Anonymous
Not applicable

Hi @v-qiuyu-msft ,

Yes I already added it as a calculated column in my SSAS cube, but it doesn't work and gives an error "This filter can't be used since the corresponding field is invalid" - see attached screenshot below.

Do you know how to check for date between 2 values use the other operators?

By the way the format you provided doesn't seem to be correct (see attached screenshot).

image.png

 

 

image.png

 

v-qiuyu-msft
Community Support

Hi @Anonymous, 

 

I would suggest you create a support ticket to let engineers look into the issue on your side. 

 

Support Ticket.gif

 

Best Regards,
Qiuyun Yu