Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi team,
I'm trying to make a new column or filter that displays the hours in my "Schedule" column from 01-01-2017 until the "Date_to" from another table "Resources". See the below example.
Currently the "Schedule" table displays ALL hours from 01-01-2017 until 31-12-2099. I want to filter this table so that I only get the hours from 01-01-2017 until the "Date_to" from another table "Resources".
How should I proceed?
You can add a column in the query editor in your "Schedule"-table like this:
if [Date] = #date(2099,12,31) then Table.SelectRows(Resources, (Res)=>Res[ResID]=[ResID]) [Date_to] {0} else [Date]
It selects the row from the Resources where the ResID match.
[Date_to] then selects the column and returns it as a list and
{0} takes the first element from that returned list and transforms it to a single value
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
this looks pretty accurate to what I need. Can I make the #date(2099,12,31) a dynamic figure?
If in my "Schedule" table I have a schedule for resource 123 from 01/01/2017 until 31/12/2019 (instead od 31/12/2099) then I would want only the schedules from 01/01/2017 until the 28-06-2017 (using the first example)
Sorry I cannot follow you here.
But you can replace the hardcoded value by a reference to another field - Just that I currently don't understand/see where this value would sit.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Sorry maybe I haven't explained well enough.
I only found time to try your solution now. By adding a column as suggested I arrived at the below result. This is the actual solution I'm trying to build:
a resource works in a company from 01-01-2017 and leaves in 31-03-2017. this information is stored on the "Resource" table. His schedule has been generated until the end of the year 31-12-2099 but the "Schedule" table has no way of knowing if the resource has left. This means that, even though the resource has left, the company is still reporting the hours the resource should have worked. For a professional services company this cannot be since we are reporting thousands of hours of people that have already left.
The solution I want to build is to know the total hours the resource has in his schedule from the beggining of the year until the date of leave.
Please create new table using the DAX below.
Table = ADDCOLUMNS(FILTER(Schedule,Schedule[Date]<=LOOKUPVALUE(Resources[DateTo],Resources[ResID],Schedule[ResID])),"NewDate",Schedule[Date])
Regards,
Charlie Liao
OK, now I understand.
1) Join your Resources to Schedule on ResID and expand [Date_to]
2) Add a column "Filter" in Schedule: if [Date_to]<=[Date] then "in" else "out"
3) Filter Schedule on new column "Filter" for "in"
This should leave only those day in where the Resource was still employed.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I'm verry sorry for my delay but I was held back by a project. How do I "join" two tables? Using the "Merge queries" tool?
Yes. A dialogue will pop up where you can choose the columns to connect.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I have tried joining them via the Merge queries tool but I get the below error.
"Expression.Error: A cyclic reference was encountered during evaluation."
What is this usually about?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
105 | |
99 | |
39 | |
30 |