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 I am new to power bi and have gotten a bit stuck.
My problem is I want to check if a value is greater than a value between a date range
(Start and end date & time).
I have one table that holds the value and the timestamp for when the value was logged.
I have a second table with the Start and end date & time I want to check between
I want to be able to filter the page by the ID column and return a table that Starts at the corresponding start date & time and ends at the corresponding end date & time.
e.g. if I filter the page by “ID 1” the table will only have the timestamps from 14/10/2021 6:00:00 am to 17/10/2021 6:00:00 am
then I can use conditional formatting on the “Value (m^3/s)” to display if a value within that data set was greater than a certain value.
Hi @jmowen
Can you create a Calculated Column and use the following:
CalCol =
VAR _DateCheck = 'tblDateTime'[Value]
RETURN
MAXX ( FILTER ( 'tblID' , 'tblID'[StartDate] <= _DateCheck && _DateCheck <= 'tblID'[EndDate] ) , 'tblID'[ID] )
Once you've established this, you can then create a relationship (1:*) from the tblID to the tblFact and use the tblID[EmployeeID] as the Slicer for filter.
Output will be as per below screenshot. My output has blanks because clearly I didn't fill out all of the TimeStamp data correctly in the sample data I put together lol 🙂
Hope this helps 🙂
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi @TheoC
Thanks for your reply
I ran into one issue, I can't have Start Dates overlap with End Dates as the start dates will overrider the Ends Dates.
e.g. with the following 2 IDs ID 1, CalCol will only go out untill the start Date of ID 2.
ID 1, Start Date 27-10-2021 10:00 AM, End Date 30-10-2021 10:00 AM
ID 2, Start Date 28-10-2021 10:00 AM, End Date 31-10-2021 10:00 AM
Please let me know if you have a solution for this problem, if not I am happy to accept the original solution as I did not communicate date would overlap in my original post.
Jared
@jmowen I ran into that on the first effort for a solution so authored the solution I presented which basically ignores any overlaps and returns based on the Employee ID.
In saying that, if you adjust the Start Date and End Date to whatever you want in your tblID, the outputs will automatically update and you do not need to change anything in the solution I have put forward 🙂
So basically adjust the Start and End Dates for ID 1 and ID 2 in the tblID and you're good to go 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Sorry maybe I’ve done something wrong, or I just don’t understand.
As my CalCol overlaps, ID 6 should go out unit the End date of 23/10/2021 1:00:00 pm, but because the ID 7 has a start date at 21/10/2021 8:00:00 am, ID 6 becomes cut off.
Even if I use the slicer
Below is what i've done. I have 2 tables and created a Calculated Column as you advised
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |