Value aggregation by delimiter in matrix values field

[Anonymous]

 Hi ,

My model data is like this :

Employee	Project start date	Project end date	Client
John	9/3/2020	9/4/2020	Abc solutions
Beth	9/2/2020	9/2/2020	Omega technologies
Beth	9/2/2020	9/2/2020	Sea technolgies
Mary	9/4/2020	9/4/2020	Horizon solutions

I created a date table and joined these tables to show  a matrix like this

employee	8/31/2020	9/1/2020	9/2/2020	9/3/2020	9/4/2020
John	Open	Open	Open	Abc Solutions	Open
Beth	Open	Open	Omega technologies	Open	Open
Mary	Open	Open	Open	Open	Horizon solutions

This matrix shows values field like this : "first client"

The problem here is beth has two tickets assigned to her and it shows only one for 9/2/2020. I want something like this for Beth with both the clients assigned to her in  a single cell by comma separation:

employee	8/31/2020	9/1/2020	9/2/2020	9/3/2020	9/4/2020
John	Open	Open	Open	Abc Solutions	Open
Beth	Open	Open	Omega technologies, Sea technologies	Open	Open
Mary	Open	Open	Open	Open	Horizon solutions

Thanks for your help.

[amitchandak]

@Anonymous , try with you date table

measure =
var _1 = MAxx(filter(Table, Table[Start Date] <=Max(Date[Date]) && Table[end Date] >=Max(Date[Date])) ,Table[Client])
return
if( isblank(_1), "Open",_1)

There should not be joined with date table, if there is a join then  use crossfilter to remove

refer : https://youtu.be/e6Y-l_JtCq4

[Anonymous]

Hi @amitchandak , 

I actually followed your  youtube video yesterday to create a date table. I followed the same intructions and did a join on date field of date table to Start date field on my data table. Should I not join it with date table ?

[Greg_Deckler · Accepted solution]

@Anonymous - Did this not work?

Well, you would need a Date table I think.

Assuming you have a Date 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 = 
    CONCATENATEX(
      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]

Thanks a ton @Greg_Deckler .I got this working. I understod what you did there. Nifty!

Have another question for you, I Currently removed all time fields from my date time column in my model and date table to get it to work as a version 1. Now if i want to introduce time back into consideration, will the same dax work for start and end date? Do I have to extend my datetable to have hourly intervals ? Any guidance is helpful.

[Greg_Deckler]

@Anonymous - Glad you got that working! Hmm, adding in time. I would need to better understand that requirement. Are you saying just adding in the time where every time value is 12:00:00 AM or are you trying to determine which hours of the day people are in meetings?

[Anonymous]

@Greg_Deckler for example my actual model had date time in it like 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

And my date table had dates with 12:00 am for every day. This resulted in data not showing up in the matrix cos of the disparity in times in model(8:30 - 5:30) and date table(12:00 AM). So I removed time element from  my model and date table to get it to work as an initial version. Now if i were to introduce the actual times back into the mix, do I have to add 24 1 hour intervals per day in my date table ? Will the dax above still work in that case ?

[Greg_Deckler]

@Anonymous - Yes, if you added 24 time intervals for each day (or 8 if you are going with a business day) then yes, the DAX would still work just fine.

So:

1/1/2020 00:00:00

1/1/2020 01:00:00

1/1/2020 02:00:00

etc.

You could generate this from your existing date table like this:

DateTime Table = 
  SELECTCOLUMNS(
    ADDCOLUMNS(
      GENERATE(
        DateTable,
        GENERATESERIES(0,23,1)
      ),
      "DateTime",[Date] & " " & FORMAT([Value],00) & ":00:00"
    ),
    "DateTime",[DateTime]
  )

[Anonymous]

@Greg_Deckler thanks so much for this. Marking it as a solution. Lots to learn for me.

also thanks @amitchandak for your inputs.