Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

@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!

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.