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 Guys,
I've got a weird one. I've got two tables linked. I have an accounts table im creating a custom column based on their first item created (from a different item table), but I want the first item created after they start.
I'm doing the following calculated column on the accounts:
Earliest Response = CALCULATE(MINX('items',[created]), FILTER('items', 'Account'[startdate] < 'items'[created] && 'account'[id] = 'items'[accountid]))
All of the accounts are unique, and an account will have multiple items of course.
It appears my filter of 'Account'[startdate] < 'items'[created] is being ignored entirely.
Solved! Go to Solution.
Hello
If I understand correctly, this is a row context thing. Your calculated column is in Account but it is iterating Item within the FILTER function and it doesn't know which AccountID it needs. So you can bring that in with a variable
This is my dummy setup:
They are related via Account ID but I'm not using that relationship. You could probably achieve the below result with RELATEDTABLE, but I've done it this way:
MIN Item Created Date =
VAR Acc_ID = Account[Accountid]
VAR Acc_Start = Account[Start Date]
VAR Tab = FILTER('Item', 'Item'[Accountid]=Acc_ID && 'Item'[created]>=Acc_Start)
RETURN MINX(Tab, 'Item'[created])
MIN Item Lookup =
VAR Acc_ID = Account[Accountid]
VAR Acc_Start = Account[Start Date]
VAR Tar_Date = Account[MIN Item Created Date]
VAR Tab = FILTER('Item', 'Item'[created]=Tar_Date)
RETURN MINX(Tab, 'Item'[itemid])
The variables grab what you need from the row of Account in order to filter Item within the FILTER function. I lookup the min date first, and then use that to identify the Item ID. If you had multiple items on the same created date, it would return the min Item ID within that subset
Hope that helps
Regards
Richard
Hello
If I understand correctly, this is a row context thing. Your calculated column is in Account but it is iterating Item within the FILTER function and it doesn't know which AccountID it needs. So you can bring that in with a variable
This is my dummy setup:
They are related via Account ID but I'm not using that relationship. You could probably achieve the below result with RELATEDTABLE, but I've done it this way:
MIN Item Created Date =
VAR Acc_ID = Account[Accountid]
VAR Acc_Start = Account[Start Date]
VAR Tab = FILTER('Item', 'Item'[Accountid]=Acc_ID && 'Item'[created]>=Acc_Start)
RETURN MINX(Tab, 'Item'[created])
MIN Item Lookup =
VAR Acc_ID = Account[Accountid]
VAR Acc_Start = Account[Start Date]
VAR Tar_Date = Account[MIN Item Created Date]
VAR Tab = FILTER('Item', 'Item'[created]=Tar_Date)
RETURN MINX(Tab, 'Item'[itemid])
The variables grab what you need from the row of Account in order to filter Item within the FILTER function. I lookup the min date first, and then use that to identify the Item ID. If you had multiple items on the same created date, it would return the min Item ID within that subset
Hope that helps
Regards
Richard
Hi Richard,
For some reason, it was the > between dates that wasn't working. I ended up creating a column on item that took the date from the account, then another that said if item date was greater than created date than 1, else 0, and then I did the filter on the 1 instead of the dates. Sloppy, but it worked.
Hi @joetech - It might help to upload a sample pbix with the issue, or you show some sample data and your date model to help understand your problem.
Many thanks
Daryl
Hi Daryl, I can't really provide a model, but essentially its something like:
Item table
Accountid | itemid | created |
456 | 123 | 2/3/2020 |
456 | 124 | 1/1/2020 |
456 | 213 | 12/1/2019 |
... | ||
952 | 531 | 8/5/2020 |
Account Table
Accountid | Start Date |
123 | 1/1/2020 |
456 | 2/2/2020 |
789 | 4/12/2020 |
163 | 8/4/2020 |
952 | 8/5/2020 |
Essentially, I want a custom column on the account table giving me the minimum date from the item table for that account, that is higher than the start date on the account table.. so for 456 it would be 2/3/2020
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 |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |