cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Data Modelling - matrix visualization - weekly/monthly/90 day view

 

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.

4 REPLIES 4
Highlighted
Super User IV
Super User IV

@darkbotrules , refer my blog on HR with Start end Date, Than can help a bit

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://youtu.be/e6Y-l_JtCq4



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

@darkbotrules - 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/...

 

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!

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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 

Highlighted

Hi @darkbotrules,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors