Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I would like to filter my data by using the slicer tool. My goal is to show only data within the last N days (or hours).
However, if I use relative filtering and filter by last 1 day (for instance), the data are not showing today's data even if I use the "include today" option.
What can I do to include also the newest data?
Cheers,
Marco
Hi @Anonymous,
We can create measure to work on it. I made one sample for your reference.
today = IF(MAX(Table1[date])=TODAY(),1,0)
last 1 day = IF(MAX(Table1[date])<=TODAY() && MAX(Table1[date])>= TODAY()-1,1,0)
Then we can filter the visual by the masure to achieve our goal.
For more details, please check the pbix as attached.
Regards,
Frank
Thank you, Frank.
That basically works. However, if I want to have a dynamic slider (the user should be able to change the time frame) this will not I guess?
Such as:
Cheers,
Marco
Hi @Anonymous,
Please try to use the formulas.
last 10 day = IF(MAX(Table1[date])<=TODAY() && MAX(Table1[date])>= TODAY()-10,1,0)
Between = IF(MAX(Table1[date])<=DATE(YYYY,MM,DD) && MAX(Table1[date])>= DATE(YYYY,MM,DD),1,0)
Regards,
Frank
Hi @Anonymous,
I can make the newest data included by this way. Could you please share your sample data to me?
Regards,
Frank
Hi @Anonymous,
Does that make sense? If so, kindly mark the answer as solution to close the case.
Regards,
Frank
@v-frfei-msft sorry for the late answer!!
Unfortunatelly that didn't work for my dataset. It still does not include the actual day. It stops at midnight before...
Here the link to a test report:
https://app.powerbi.com/view?r=eyJrIjoiY2VhYzcwZWYtZWJhZC00ZjUwLWE3OTgtMTcxZjdhYTYwOThlIiwidCI6ImIwZ...
Here the detailed request of the data:
let Source = Xml.Tables(Web.Contents("http://dataservices.tasman.govt.nz/environmentalgraph/11/741/4/week/4/week/47/0/600/300/data?format=xml")), Table0 = Source{0}[Table], #"Changed Type" = Table.TransformColumnTypes(Table0,{{"AltSiteName", type text}, {"Latitude", type number}, {"Longitude", type number}, {"SiteId", Int64.Type}, {"SiteName", type text}}), #"Expanded Tests" = Table.ExpandTableColumn(#"Changed Type", "Tests", {"Test"}, {"Tests.Test"}), #"Expanded Tests.Test" = Table.ExpandTableColumn(#"Expanded Tests", "Tests.Test", {"Datasource", "Measurement", "MeasurementId", "Name", "Results", "Unit"}, {"Tests.Test.Datasource", "Tests.Test.Measurement", "Tests.Test.MeasurementId", "Tests.Test.Name", "Tests.Test.Results", "Tests.Test.Unit"}), #"Expanded Tests.Test.Results" = Table.ExpandTableColumn(#"Expanded Tests.Test", "Tests.Test.Results", {"Result"}, {"Tests.Test.Results.Result"}), #"Expanded Tests.Test.Results.Result" = Table.ExpandTableColumn(#"Expanded Tests.Test.Results", "Tests.Test.Results.Result", {"DT", "NonDetect", "Status", "Value"}, {"Tests.Test.Results.Result.DT", "Tests.Test.Results.Result.NonDetect", "Tests.Test.Results.Result.Status", "Tests.Test.Results.Result.Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Tests.Test.Results.Result",{"Tests.Test.Results.Result.NonDetect", "Tests.Test.Results.Result.Status"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Tests.Test.Results.Result.DT", type datetimezone}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Tests.Test.Results.Result.Value", "Value"}}), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tests.Test.Results.Result.DT", type datetime}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Easting", "GraphBytes", "GraphTitle", "Northing", "Tests.Test.Datasource", "Tests.Test.Measurement", "Tests.Test.MeasurementId", "Tests.Test.Name"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Tests.Test.Results.Result.DT", "Date"}}) in #"Renamed Columns1"
So this effects not only PowerBI Desktop but also the webbased PowerBi
@v-frfei-msft? anyone?
I conntacted the support but they aren't coming back to me. 😞
Has anyone an idea?
Thank you for your reply, Frank. You are a legend.
I'm sorry, but I'm afraid that I described my problem inarticulately.
With your formulas, you have predefined filter options (1 day, or 10 days). However, what I want to have is the slicer tool which also includes the current day. At the moment, it does not, even I tick 'Include today' in the options.
Do you think it's a time setting anywhere in the report properties?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |