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,
Is it possible to only return records from a table where the records has a StartDate and an EndDate that is between the user selected date from another unrelated table? For example when the user selects a value (date) from a slicer linked to another table and the SELECTEDVALUE is between the StartDate and EndDate.
At this point I am not even sure how to join/create a relationship between these two tables. I don't neccessary need to have any relationship between the two tables, but just need some way to use what was selected from the EffDateTable to filter what is returned on the OnLeaveTable (and also another table).
TIA!
Kevin
Solved! Go to Solution.
@sd_kevin - So this is like what I call a Complex Selector. In your case it could go like this:
Measure =
VAR __Date = SELECTEDVALUE('EffDateTable'[Effective Date])
VAR __LeavdDate = MAX('OnLeaveTable'[LeavdDate])
VAR __ReturnDate = MAX('OnLeaveTable'[ReturnDate])
RETURN
IF(__LeavdDate >= __Date && __ReturnDate <= __Date,1,0)
You can then use your OnLeave column in a table visualization and then Measure in the Filters pane for example.
@Greg_Deckler @mahoneypat @sevenhills @amitchandak
Thank you all for your solutions and quick responses!!! I love this community! I learned so much just reviewing all the solutions you provided. All the solutions worked. Since @Greg_Deckler responded first I will mark his as the solution.
Thank you all again. Your help was very much appreciated!
Warm regards,
Kevin
@sd_kevin , Try a measure like
Measure =
var _max = maxx(allselected(EffDateTable),EffDateTable[EffectiveDate])
return
Calculate(Count(Epluyees), OnLeaveTable[LeaveDate]<= _max, OnLeaveTable[ReturnDate]>=_max)
Yes. With no relationship between the tables, make a slicer with the EffectiveDate and a table visual with your onleave column (and leave and return date if desired), and this measure:
SelectedDateInRange = var selecteddate = selectedvalue(EffDateTable[EffectiveDate])
return Calculate(Countrows(OnLeaveTable), OnLeaveTable[LeaveDate]<= selecteddate, OnLeaveTable[ReturnDate]>=selecteddate)
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Optional: If you are taking above @mahoneypat, use today date for null Return date
var selecteddate = selectedvalue(EffDateTable[Effective Date])
return Calculate(Countrows(OnLeaveTable), OnLeaveTable[LeaveDate]<= selecteddate, IF(ISBLANK(OnLeaveTable[ReturnDate]), TODAY(), OnLeaveTable[ReturnDate]) >=selecteddate)
@sd_kevin - So this is like what I call a Complex Selector. In your case it could go like this:
Measure =
VAR __Date = SELECTEDVALUE('EffDateTable'[Effective Date])
VAR __LeavdDate = MAX('OnLeaveTable'[LeavdDate])
VAR __ReturnDate = MAX('OnLeaveTable'[ReturnDate])
RETURN
IF(__LeavdDate >= __Date && __ReturnDate <= __Date,1,0)
You can then use your OnLeave column in a table visualization and then Measure in the Filters pane for example.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |