Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Termigez
Regular Visitor

Check for value

datehomeaway
17.10.2018af
18.10.2018ab
19.10.2018fc

 

I want to create a new calculate column to check whether a value in 'home' column is present in either 'home' column or 'away' column only for rows with yesterday's date. So for example for a game on 19.10.2018 the formula would return blank because 'f' didn't play yesterday - his last game was 2 days ago. But for a game on 18.10.2018 the formula would return something ("yes", "1") etc. because 'a' indeed played a game on 17.10.2018. Thank you!

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@Termigez 

sorry, it was my bad

 

 

Column = 
var _playedYesterday = countrows(filter('Table','Table'[date]=EARLIER('Table'[date])-1 && ('Table'[home]=EARLIER('Table'[away]) || 'Table'[home]=EARLIER('Table'[home]))))
return
if(_playedYesterday>0,"yes","no")

 

 

should be correct for back-to-back games for home team 

I love sports stats! 🙂


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

8 REPLIES 8
az38
Community Champion
Community Champion

hi @Termigez 

try a column

Column = 
var _playedYesterday = countrows(filter('Table','Table'[date]=EARLIER('Table'[date])-1 && ('Table'[away]=EARLIER('Table'[away]) || 'Table'[home]=EARLIER('Table'[home]))))
return
if(_playedYesterday>0,"yes","no")

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi, it is super close! However right now this formula doesn't tell me whether home team or away team played yesterday. It returns "yes" if one of those teams played yesterday but doesn't specify which one. I would like to verify this condition just for a home team, whether it played yesterday as a home team or away team. 

az38
Community Champion
Community Champion

@Termigez 

it is exactly what you asked for in the first post.

give me example of desired output, please, to complete solution 🙂


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Sorry, I am not a native speaker so maybe I didn't specify my desired outcome well enough. 

 

datehomeawayb2b
17.10ABno
17.10CDno
18.10EDno
18.10AFyes
18.10BXyes
19.10CDno
19.10FGyes

 

longer example. We don't have rows with date before 17.10 so the formula returns no. Then for 18.10 - for the first row it returns 'no' because E didn't play yesterday. D played yesterday but I am not interested in it (right now your formula returns 'yes' for this row). Second row for 18.10 returns 'yes' because home team A played yesterday as a home team. Third row for 18.10 returns 'yes' because home team B played yesteday as an away team. First row for 19.10 returns 'no' because C didn't play yesterday and I don'y care about an away team D. Second row for 19.10 returns 'yes' because once again home team played yesterday as an away team. 

 

So in a nutshell: I only care for a home team whether it played yesterday in any game (no matter it was home or away game)

az38
Community Champion
Community Champion

@Termigez 

sorry, it was my bad

 

 

Column = 
var _playedYesterday = countrows(filter('Table','Table'[date]=EARLIER('Table'[date])-1 && ('Table'[home]=EARLIER('Table'[away]) || 'Table'[home]=EARLIER('Table'[home]))))
return
if(_playedYesterday>0,"yes","no")

 

 

should be correct for back-to-back games for home team 

I love sports stats! 🙂


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

pay attention, I just correct statement in my last post

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

well actually your changed version returns wrong values, the previous one was perfect

az38
Community Champion
Community Champion

@Termigez ok, I fixed it 🙂


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.