cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

How to get first row in Filter function

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] )
)
1 ACCEPTED SOLUTION

Accepted Solutions

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

edhans_0-1603472615425.png

 

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Microsoft
Microsoft

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.

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
UserIDCost/HourMonth ActiveYear Active
1 $                 10.00Jan2020
1 $                 15.00May2020
1 $                 18.50Sep2020
2 $                   5.00Jan2020
2 $                   6.00Jun2020
3 $                 22.00Jan2020
3 $                 25.00Sep

2020

 

WorkLog Table 
UserIDWorkHourLogDateMonthYearGet CostHour from UserCost based on Closest Month/Year
110January 1, 2020Jan2020 $                                    10.00So 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
120February 1, 2020Feb2020 $                                    10.00
140March 1, 2020Mar2020 $                                    10.00
122April 1, 2020Apr2020 $                                    10.00
133May 1, 2020May2020 $                                    15.00
244January 1, 2020Jan2020 
21February 1, 2020Feb2020 
22March 1, 2020Mar2020 
233June 2, 2020Jun2020 
255July 1, 2020Jul2020 
122June 1, 2020Jun2020 $                                    15.00
177July 1, 2020Jul2020 $                                    15.00
132August 1, 2020Aug2020 $                                    15.00
146September 1, 2020Sep2020 $                                    18.50
12October 1, 2020Oct2020 $                                    18.50
31January 1, 2020Jan2020 
32September 1, 2020Sep2020 
38October 1, 2020Oct2020 

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

edhans_0-1603472615425.png

 

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Regular Visitor

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.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors