Forum Discussion
Helper IHow to write a measure that filters data based on a date selection on a visual
This is how I would write it. Please check if it matches your expectations.
Baseline = var h = SELECTEDVALUE(Sheet1[Time]) var d = SELECTEDVALUE(Sheet1[Date]) var a = CALCULATETABLE(Sheet1,allselected(),Sheet1[Date]<=d,Sheet1[Time]=h,Sheet1[WeekdayType]="Weekday") var b = TOPN(20,a,[Date],DESC) var c = TOPN(15,b,[HourlyAverage],DESC) return averagex(c,[HourlyAverage])What should happen if fewer than 20 data points are available?
Helper I
Super UserThis is how I would write it. Please check if it matches your expectations.
Baseline =
var h = SELECTEDVALUE(Sheet1[Time])
var d = SELECTEDVALUE(Sheet1[Date])
var a = CALCULATETABLE(Sheet1,allselected(),Sheet1[Date]<=d,Sheet1[Time]=h,Sheet1[WeekdayType]="Weekday")
var b = TOPN(20,a,[Date],DESC)
var c = TOPN(15,b,[HourlyAverage],DESC)
return averagex(c,[HourlyAverage])What should happen if fewer than 20 data points are available?
- JohnnyCenaa
Thank you.
This is very close to what I am trying to do.
It worked for some of the datasets. I then went further down (since I have a larger dataset) and noticed that the Baseline being returned is slightly off (I have a separate database with the baseline values for each hour and day).
Do you have any idea why it seems to be slightly off when I move further down the dates?
If there are fewer than 20 data points I would like it to return an error saying something like "Baseline could not be calculatted due to missing data points"
Thanks a lot for the help with this!!
Any ideas as to why the slight discrepancies?
The difference between what your measure calculates and the actual baseline I have is only about 10 to 15.
Asides all that, your measure delivers the expected result. Thank you.
If you have any ideas or suggestions on how it can be improved, please share.
Thank you once again!
- lbendlin
- my baseline includes the current day. Maybe yours doesn't. You can adjust the code accordingly.
- you cannot return numbers or text in the same measure. Your only option is to suppress the result by returning BLANK() if there are fewer than 20 data points. Again, you can adjust the measure accordingly.
- JohnnyCenaa
That's it; mine does not include the current day. I will adjust the code accordingly.
Thanks a lot!
- JohnnyCenaa
Hi,
Thanks for the help earlier.
Currently I am adjusting the code you provide to suit my requirements.
I want it now return the sum of HourlyAverage between 1pm and 3pm after the average has been calculated. But it returns a sum way more than the time range given
Please could you tell me what i am doing wrong. Thank you.This is the code below
IDABaseline =VAR h = SELECTEDVALUE(Query5[Time])VAR d = SELECTEDVALUE(Query5[Date])VAR a =CALCULATETABLE(Query5,ALLSELECTED(),Query5[Date] < d,Query5[Time] = h,Query5[WeekdayType] = "Weekday")VAR b = TOPN(20, a, [Date], DESC)VAR c = TOPN(15, b, [HourlyAverage], DESC)VAR avgHourly =AVERAGEX(c,[HourlyAverage])VAR filteredHours =FILTER(c,Query5[Time] >= TIMEVALUE("1:00 PM") && Query5[Time] <= TIMEVALUE("3:00 PM"))RETURNIF(COUNTROWS(b) = 20,SUMX(filteredHours, [HourlyAverage]),BLANK()) - lbendlin
The baseline is calculated based on the exact time slot (regardless which one - whatever your filter context is) . You cannot then arbitrarily expand that to cover other timeslots. You'll have to base that computation on the raw data, not the baseline.
- JohnnyCenaa
Okay, but I am trying to add the values in the time slot for the baseline calculated. The baseline is the data I want to work with to get the sum for the 3 hours mentioned.
- lbendlin
As I said - your timeslot range will have to be a separate measure. You can then compare that to the baseline measure. Or use the graphical solution where you show the actuals and the baseline side by side.
- JohnnyCenaa
Sorry about that, I didn't fully understand what you said earlier.
I followed what you said and created a calculated table that filters my main data to the time range I want .
I then wrote this measure below but it gives totally wrong valuesSUMX(TimeSlotTable,CALCULATE([Baseline], TimeSlotTable[Time]))Please what am I doing wrong
- lbendlin
would you mind posting a sample pbix? Or use mine and add your formulas?
- JohnnyCenaa
My version of Power BI is older than yours, so I couldn't use the file you sent.
I have attached a sample data file.If you run into any issues with the file let me know.
All the measure I have written so far are on it. On Page 1 the visual shows the measures I have been playing around with. IDABaseline is the name of the main measure I am working on, the IDABaselineTest and SumBetween 1PMAnd3PM measures are test measures I have been trying out.
Thanks for the help.
https://drive.google.com/file/d/1q6Cha_ddpD8x5sUL6e93SyxCkcHJGoYo/view?usp=sharing
- lbendlin
Note that the baseline measure is calculated for every single time slot separately.
That's one of the reasons why your sum is so high. If you want the baseline to be pegged at 3pm each day then you need to modify the measure.
What is this WeekDay Type "Event" ?
The TimeslotTable doesn't help, it only introduces additional cartesians.
- JohnnyCenaa
How can I modify the measure to just give me values between 1 pm and 3 pm. if you look at the Event Table, you will see that the start and end time I want can change, but before I get there I wanted the measure to at least work for one-time range.
The sum I want is the sum of the calculated Baseline between a certain time range.
The Weekdaytype Event is just so that this calculation is only done for days I have tagged as an Event, I do not need it calculating for all the dates I have. That is why in the Event Table I specified the day and time range I want.
The TimeslotTable will be removed.
How can I better write the measure to give the desired result
Thank you!
- lbendlin
the start and end time I want can change, but before I get there I wanted the measure to at least work for one-time range. The sum I want is the sum of the calculated Baseline between a certain time range.You lost me on this one. I am less and less sure what you are trying to achieve. Can you maybe restate it, maybe with some more context?
- JohnnyCenaa
Sorry about the confusion.
The measure I am trying to write is to calculate the sum of the Baseline calculated between a time range. So after the Baseline is calculated I want another measure to give the sum of the values between a specific time range. The Event Table has the time range i want, which is the IDA start time and IDA end time columns. and that also changes based on the eventdate column
- JohnnyCenaa
Hi,
Sorry to disturb you, please do you have an idea how I can write this measure?
Was the explanation I gave previouly clearer? If you need anymore info please let me know and I will provide it.