How to get first row in Filter function

[Drako]

Hi,

   I have been struggling with this for some time. We have 2 tables. One that has Logs (workLog) of tracked time and another that has cost/hour (usercost) in specific dates. This is because the cost of a user can change during time. The usecost table has the ID of the user a Datetime Activated when the new cost is added is to be used from this point onword and the field cost.

   What I'm doing is adding a column in the  Worklog and adding this formula. This works perfect when the Specific User only has 1 record on the the UserCost table. What I need is to only return the frist record it finds. but using filter as below. I also wanted to order by Worklog(CreatedYear) and Worklog(CreatedMonth).

CostUserHour =

CALCULATE(Values(UserCost[CostPerHour]),

FILTER

(UserCost,UserCost[UserID] = WorkLog[accountId] && UserCost[YearActivated] <= WorkLog[CreatedYear] && UserCost[MonthActivated] <= WorkLog[CreatedMonth] )

)

[V-lianl-msft]

Hi @Drako ,

For example, we get the minimum datetime of each user as the filter condition, and then calculate the cost column.

Try:

col = 
VAR MIN_Date = CALCULATE(MIN('Table'[date]),ALLEXCEPT('Table','Table'[user]))
RETURN IF('Table'[date]=MIN_Date,'Table'[cost])

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

Please remove any sensitive data before uploading.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Drako]

Hi @V-lianl-msft  and @edhans 

    I'm still not able to get what I need. I have created an excel with the example data. Please see one drive link. Also I have pasted the tables here for example.... What I think is missing is some sort of MAX when I get more that I records in the filter but I have not been able to do this...

Excel: https://1drv.ms/u/s!AvX2iSHtfUKqq8xgaAPqkfs_c8HkWQ?e=K40Mrd 

Tables:

UserCost Table
UserID	Cost/Hour	Month Active	Year Active
1	$                 10.00	Jan	2020
1	$                 15.00	May	2020
1	$                 18.50	Sep	2020
2	$                   5.00	Jan	2020
2	$                   6.00	Jun	2020
3	$                 22.00	Jan	2020
3	$                 25.00	Sep	2020

WorkLog Table
UserID	WorkHourLog	Date	Month	Year	Get CostHour from UserCost based on Closest Month/Year
1	10	January 1, 2020	Jan	2020	$                                    10.00	So what this column should get is the closest Hourly Rate for that especific month from th CostHour table. I've added the  values as example. So for userID = 1 you can see several diferent rates. The problem: If you see line 5 if I search for the cost ikn the USerCost this will work perfectly, becuase it will return 1 ROW in the table.  But if you go to line 8 then when I apply USercost[Month] <= Worklok[Month] && Usercost[Year] <= Worklog[Year] it will return 2 rwos from the USerCost.  One with $10 and the other for $15, I need to get the MAX from the month... I have tried several MAX fucniontions in the formula I used without sucess
1	20	February 1, 2020	Feb	2020	$                                    10.00
1	40	March 1, 2020	Mar	2020	$                                    10.00
1	22	April 1, 2020	Apr	2020	$                                    10.00
1	33	May 1, 2020	May	2020	$                                    15.00
2	44	January 1, 2020	Jan	2020
2	1	February 1, 2020	Feb	2020
2	2	March 1, 2020	Mar	2020
2	33	June 2, 2020	Jun	2020
2	55	July 1, 2020	Jul	2020
1	22	June 1, 2020	Jun	2020	$                                    15.00
1	77	July 1, 2020	Jul	2020	$                                    15.00
1	32	August 1, 2020	Aug	2020	$                                    15.00
1	46	September 1, 2020	Sep	2020	$                                    18.50
1	2	October 1, 2020	Oct	2020	$                                    18.50
3	1	January 1, 2020	Jan	2020
3	2	September 1, 2020	Sep	2020
3	8	October 1, 2020	Oct	2020

[edhans · Accepted solution]

@Drako - I am not 100% sure I am following, but see if this helps. This is what I have:

Cost Per Hour =
VAR varCurrentUser =
    MAX( 'Work Log'[UserID] )
VAR varCurrentMonthEnd =
    EOMONTH(
        MAX( 'Work Log'[Date] ),
        0
    )
VAR Result =
    CALCULATE(
        MAX( 'User Cost'[Cost/Hour] ),
        FILTER(
            ALL(
                'User Cost'[UserID],
                'User Cost'[Date]
            ),
            'User Cost'[UserID] = varCurrentUser
                && 'User Cost'[Date] <= varCurrentMonthEnd
        )
    )
RETURN
    Result

You can see my PBIX file here. You will notice I did some data shaping and modeling in Power Query because I don't see how to get the results give how your tables are formed. For example, having Jan in one column and 2020 in another isn't very useful, so I created a date for the last day of the month for that column, which can be filtered on and related to an actual date table. You will need to point the data source back to your Example.xlsx file on your hard drive to see the Power Query work using the Data Source Settings icon in the Home Ribbon of PQ.

If that is not what you are expecting, can you be more specific on how the calculation should work?

[Drako]

Hi @edhans ,

   I downlaoded you PBIX and saw your solution. It is exactly how I need it. Question  I don't use mich the date table you added but I have seen it is very usefull. How did you create this, manually? Or is thier some fucntion or something in power bi to create it?

   Thanks a LOT...it did work and I'm adjusting my PwerBI to have the dates as you added and the formula.

[edhans]

Hi @Drako - glad I was able to help. As for my date table, see this url - https://bit.ly/DateTableByEd - it is 100% generated in Power Query. There is a full explanation of how it works, and a link to a blog I did last year that will allow you to turn the thing into a fully dynamic date table that will move forward in time with new data over the years.

That is in my personal Evernote notebook, so I am often updating and enhancing it.

[edhans]

There is no first record @Drako - records are stored however the data model wants to store them. My date tables almost always show a July date first in the Data view as example, but the tables always start Jan 1.

You need to identify that first row based on critera. For example:

CostUserHour =
VAR varThisUser =
    MAX( WorkLog[AccountId] )
VAR varThisYear =
    MAX( WorkLog[CreatedYear] )
VAR varThisMonth =
    MAX( Worklog[CreatedMonth] )
RETURN
    CALCULATE(
        VALUES( UserCost[CostPerHour] ),
        FILTER(
            UserCost,
            UserCost[UserID] = varThisUser
                && UserCost[YearActivated] <= varThisYear
                && UserCost[MonthActivated] <= varThisMonth
        )
    )

would get you started. This would be a measure. I avoid calculated columns as much as possible.

But to really help, we'd need to see some data with expected results.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum