Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
After my latest data refresh one of my formula is no longer working. DTO Rates hasn't changed but we have more lines in TRSBookings
It's probably overcomplicated but I want to compare the custom columns from 2 TRSBookings and DTO Rates then return the DTO Rates[Rate], if the custom doesn't provide a match then compare only the TRS/Request_No, if no match on either the value should be blank/0/null.
Request_No | Team_ID | Custom | DTORate |
R042258 | 1100 | R042258 1100 | 715.62 |
R041506 | 1192 | R041506 1192 | 533.89 |
R041506 | 1198 | R041506 1198 | 408.5 |
R041101 | 1198 | R041101 1198 | |
R041506 | 1192 | R041506 1192 | 533.89 |
DTO Rates
TRS | Team_ID | Custom | Rate |
R042258 | 715.62 | ||
R041506 | 1192 | R041506 1192 | 533.89 |
R041506 | 1198 | R041506 1198 | 408.5 |
Could someone please help or point me in the right direction?
Solved! Go to Solution.
@mdex , create a new column in TRSBookings
maxx(filter('DTO Rates', 'DTO Rates'[TRS]= TRSBookings[REQUEST_NO && 'DTO Rates'[Custom] =TRSBookings[Custom] && 'DTO Rates'[Team_ID] =TRSBookings[Team_ID]),'DTO Rates'[Rate])
if need you can remove Team_ID if not needed
@mdex , create a new column in TRSBookings
maxx(filter('DTO Rates', 'DTO Rates'[TRS]= TRSBookings[REQUEST_NO && 'DTO Rates'[Custom] =TRSBookings[Custom] && 'DTO Rates'[Team_ID] =TRSBookings[Team_ID]),'DTO Rates'[Rate])
if need you can remove Team_ID if not needed
Hi Amit,
Thanks for taking a look. I realise there was a slight discrepany with my example data. DTO Rate column is being calculated in TRSBookings I incorrectly had this in the table as rate.
The rate for R042258 was also missing from both tables. The view you see if my expected result.
Your formula works ok where custom columns match, but if no custom match then just the TRS/Request_NO should be compared.
Hope this makes sense.
EDIT: Think I have it!
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
31 | |
27 | |
15 | |
13 | |
10 |