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 sorry for all the questions but you guys are so helpful!
I would like to write a code that populates 'last check up' in table 1 with the date in table 2 based on the name of the footballer but only if the check up date is after the last game of football the player has played.
Please let me know if there is a way to do this?
Thanks,
Gordon
Solved! Go to Solution.
Hi @G0ggs ,
You could create a column by the following formula:
Last check up =
VAR _date =
CALCULATE (
MAX ( 'Chek up dates'[Date] ),
FILTER ( ALL ( 'Chek up dates' ), [Name] = EARLIER ( [Footballer name] ) )
)
VAR _dif =
DATEDIFF ( [Last time played football], _date, MONTH )
RETURN
IF ( _date > [Last time played football] && _dif <= 6, _date )
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @G0ggs ,
You could create a column by the following formula:
Last check up =
VAR _date =
CALCULATE (
MAX ( 'Chek up dates'[Date] ),
FILTER ( ALL ( 'Chek up dates' ), [Name] = EARLIER ( [Footballer name] ) )
)
VAR _dif =
DATEDIFF ( [Last time played football], _date, MONTH )
RETURN
IF ( _date > [Last time played football] && _dif <= 6, _date )
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey it is showing "A table of multiple values was supplied where a single value was expected."
I have the names of the footballers multiple times and multiple times played football, does this cause an issue?
Thanks,
Gordon
@G0ggs
Not sure how your model is organized. See my example below:
Table 02
Table 01
Compare with yours.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hey Fowmy great thanks, ah sorry one other requirement is the date has to be within 6 months of the last time football played rather than just if the check up date is after the last time played, how do I add this in please?
@G0ggs
As requested:
Lat Checkup Date =
VAR __DATE = LOOKUPVALUE(Table02[Date],Table02[Name],[Footballer name])
VAR _PLAYEDDATE = Table01[Last time played football]
VAR RESULT =
IF(
__DATE < _PLAYEDDATE && __DATE >= EDATE( _PLAYEDDATE , -6 ), __DATE)
RETURN
RESULT
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@G0ggs
Add the following Column to your Table1:
Lat Checkup Date =
VAR __DATE = LOOKUPVALUE(Table02[Date],Table02[Name],[Footballer name])
VAR RESULT = IF( __DATE > Table01[Last time played football], __DATE)
RETURN
RESULT
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |