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.
Hello at all,
i'm looking for a possibility to get all records between two dates from a slicer.
That's my slicer for selecting min and max-date.
Her two measures for getting min and max from slicer:
Solved! Go to Solution.
Hi at all,
thanks for the answers.
An addendum again.
In the timeline below, the values of both substitutedStart and substitutedEnd are shown.
minDateTime_Selected and maxDateTime_Selected are determined as follows.
In future, however, minDate_Selected and minTime_Selected as well as maxDateSelected and maxTimeSelected should no longer be hard coded but be selectable via a slicer.
My main problem is that I get the values that come from a slicer not in the editor.
So I can not expect it.
Maybe anyone knows.
Thanks, Gresi
also I noticed in your min/max calculation
you might need to clear all the visual level filters coming from the related visuals. Removing visual level filters really based on the visuls that you are using on the report.
If you are using direct query then there is an issue with milliseconds in the time stamps. I had this issue when I try to calculate min and max time in a DQ dataset. Its a analysis services issue @marcorusso has a white paper about the same issue.
If that is the case you may want to truncate the ms part of the timestamp and use the tuncated timestamp in the min and max calculation and comparison.
Hi @Gresi
It loos like you are creating a calculated table. These are static and will not be affected by slicers. You could use the start and end date in MergedTasks directly on the slicers
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi AIB,
thanks for the answer.
Should i use two slicers ? One for start and one for end ?
And how should i substitute start an end when they are overlapping like this ?
I can not do like:
Have a look aat what's done here with the filter for the visual and see if it helps:
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
@AIB
Hi, thanks for your answer.
But i can't do it.
Would you download the example to show me how i can i solve the issue ?
I only want to see all records of the 06.08.2019.
There should be two replaced values.
The start 05.08.2019 23:21:25 - end 06.08.2019 01:45:15 should change to start 06.08.2019 00:00:00- end 06.08.2019 01:45:15 and
start 06.08.2019 19:17:36- end 07.08.2019 05:43:59 should change to start 06.08.2019 19:17:36- end 06.08.2019 23:59:59.
It would be nice if you could solve that.
Thanks, Gresi
Hi @Gresi
When clicking on the link, it blocks so i can't find any sample data or screenshots.
Is your sample data like
start date | end | index |
8/5/2019 23:21 | 8/6/2019 1:45 | 1 |
8/6/2019 19:17 | 8/7/2019 5:43 | 2 |
What is your final expected result?
calcualte days for each index which is in the period of selected min and max date?
Best Regards
Maggie
Hi Maggie,
sorry, my data looks like this.
I want to have all records from 06.08.2019 00:00:00 to 06.08.2019 23:59:59.
The start value from the first row and the endvalue from the last row should be replaced because they are overlapping.
Hi @Maggie,
does this link work ?
https://drive.google.com/file/d/1F7mPUSSWg4hMklvgrl2UeZYAa6UIg_44/view?usp=sharing
Thanks, Gresi
Thanks for sharing the PBIX file.
I see that there is no relationship in between the tables, can you please create a relationships in the data model and try.
Hi @venal,
thanks for your reply !
Now i have two slicers, one for startdate and one for enddate.
Startdate has a relationship to MergedTasks[startDate] and enddate has a reationship to MergedTasks[endDate].
The slicer for the hours can be neglected.
https://drive.google.com/file/d/11aRKfPvHYPRkDl0Rp7ZxU7nkP8cdeLFH/view?usp=sharing
I only what to show the tasks between these dates including the trimmed tasks.
For example:
startdate -> 06.08.2019
enddate -> 07.08.2019
Now all tasks from 06.08.2019 are be displayed.
The first and the last task should be cut off because the first start on the 05.08.2019 and the last ends on the 07.08.2019.
I only wanna see all tasks from the 06.08.2019.
After cut off the first task should start on the 06.08.2019 00:00:00 and the last task should end on the 06.08.2019 23:59:59.
I hope you understand.
Thanks, Gresi
Hi @Gresi
First delete the relationship so to have model below
Create a caluclated column in "merged task" table
date-time = [Date]&" "&[Time]
Change it to date time format
Then Create measures
Measure = VAR start1 = MIN ( CalendarStartDays[startdate] ) VAR end1 = MAX ( CalendarEndDays[enddate] ) RETURN IF ( MAX ( MergedTasks[Date] ) > start1 && MAX ( MergedTasks[Date] ) < end1, 1, 0 )
In this way, it will filter only 2019/8/6.
What do you like for "hour" slicer?
Use the slicer to limit the end hour of the that date(2019/8/6)?
Hi at all,
thanks for the answers.
An addendum again.
In the timeline below, the values of both substitutedStart and substitutedEnd are shown.
minDateTime_Selected and maxDateTime_Selected are determined as follows.
In future, however, minDate_Selected and minTime_Selected as well as maxDateSelected and maxTimeSelected should no longer be hard coded but be selectable via a slicer.
My main problem is that I get the values that come from a slicer not in the editor.
So I can not expect it.
Maybe anyone knows.
Thanks, Gresi
Hello everybody,
can someone help me please ?
That would be wonderful, I can not get any further
Thank you in advance,
Gresi
Hi @Gresi
First please note:
calculated columns can't change with slicer.
We can't add date and time by "+", it doesn't produce a correct date time format value.
I am working to find a solution for you, sorry for late.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |