Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
Had to recreate the post as I am unable to post edits to my previous post.
A power BI novice here.
I am connected to an on prem sql server. I have done some filtering on the data to get it to show as below :
Employee | Project start date | Project end date | Client |
John | 9/3/2020 8:30:00 AM | 9/4/2020 5:30:00 PM | Abc solutions |
Beth | 9/2/2020 11:30:00 AM | 9/2/2020 12:30:00 PM | Omega technologies |
Beth | 9/2/2020 1:30:00 PM | 9/2/2020 5:30:00 PM | Sea technolgies |
Mary | 9/4/2020 1:30:00 PM | 9/4/2020 5:30:00 PM | Horizon solutions |
Now i want to convert this data into a weekly view where the weekdays become columns and the corresponding data for clients goes under the appropriate weekday column for that employee as shown below. If the employee doesn't have an engagement for that day then we show them as open. :
employee | 8/31/2020 | 9/1/2020 | 9/2/2020 | 9/3/2020 | 9/4/2020 |
John | Open | Open | Open | Abc Solutions: 8:30 am - 5 :30 pm | Abc Solutions: 8:30 am - 5 :30 pm |
Beth | Open | Open | Omega technologies: 11.30 am – 12.30pm Sea technologies : 1 :30 pm – 5 :30 pm | Open | Open |
Mary | Open | Open | Open | Open | Horizon solutions:1:30 pm – 5:30 pm |
I have been trying to do this but it's either hardcoding or feels like a hack. I am Almost on the verge of giving up on power bi and trying to build a asp.net. app. I can do this on c# but it's time taking. How does one approach this ? Any suggestions or solutions are highly appreciated.
@Anonymous - Well, you would need a Date table I think. You would then need something like Open Tickets - https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Or you may need this thing I just did with Overlap - https://community.powerbi.com/t5/Desktop/Properly-summing-up-time-spent-in-overlapping-appointments/td-p/1327178/highlight/false/page/2
Assuming you have a Week column in the Columns of your matrix and Employee in rows, maybe something like lookup range:
https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430
Measure =
VAR __Employee = MAX('Table'[Employee])
VAR __Date = MAX('Calendar'[Date])
VAR __Client =
MAXX(
FILTER(
ALL('Table'),
[Employee] = __Employee && __Date >= [Project start date] && __Date <= [Project end date]
),
[Client]
)
RETURN
IF(ISBLANK(__Match),"Open",__Client)
Oh yeah, Lookup Range ought to do it!
I didn't really understand the lookup range concept. Kinda new to Dax and all. So I went ahead with the date table route. Created a datetable and linked it to my data table. Have some basic visualization set up in a matrix. For my company, most of the start and end dates have times from 8:30 am - 5:30 pm. If I want to set up start and end dates along with their times like shown above, should I create a date table with hourly intervals for each day ? Thanks for your help @Greg_Deckler
Hi @Anonymous,
I do not so recommend you accurate mapping all dates with hour intervals, it will generate huge amount of records and affect the performances.
In my opinion, you can create a expand table on date level to mapping raw table records and a standalone time table(it does not have any relationship to other tables) for further dax formula calculation.
You can take a look at the following link to create a calculated table with expanding records which generated from raw table date fields:
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
@Anonymous , refer my blog on HR with Start end Date, Than can help a bit
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |