Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 th...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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!

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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