Conditional Path function

[mvananaken]

Hello

Imagine the following table in PowerBI view. This data comes from 2 related tables (employee and matchprofiles, 1:N)

Employee      MatchProfileID      Shortdescription  -          Othertext            Startdate

Employee A   1                                 Hello                                 Bonjour               2016-01-01

Employee A   2                                 Something                        Something2        2016-02-01

Employee A   3                                 Yess..                                  No                      2016-12-10

Employee A   4                                 Brr.                                     Brr22                   2016-12-30

I’m looking for a merge function for shortdescription & Othertext  of multiple rows with a startdate the upcoming 30 days. The result in a table should be something like this is a PowerBI table view:

Hello,

Imagine the following table in PowerBI view. This data comes from 2 related tables (employee and matchprofiles, 1:N)

Employee      MatchProfileID      Shortdescription  -          Othertext            Startdate

Employee A   1                             Hello                               Bonjour                2016-01-01

Employee A   2                             Something                      Something2        2016-02-01

Employee A   3                             Yess..                                No                      2016-12-10

Employee A   4                             Brr.                                  Brr22                  2016-12-30

Employee is related to the other table (1:N)

I’m looking for a merge function for shortdescription & Othertext  (multiple rows) with a startdate the upcoming 30 days. The result in a table should be something like this is a PowerBI table view:

Employee           Mergedescription&Othertext   

Employee A       Yess&No, Brr.Brr22                                        -> merges in one field.

I tried something with “PATH”, but got an error about not containing values because the fields I want to show not are the table primary en secondary keys.

Employee A       Yess&No, Brr.Brr22                                        -> merges in one field.

I tried something with “PATCH”, but got an error about not containing values because the fields I want to show not are the table primary en secondary keys.

First: Is this possible in a medium sized dataset? Any ideas to achieve this?  

[v-huizhn-msft]

>> I’m looking for a merge function for shortdescription & Othertext  of multiple rows with a startdate the upcoming 30 days.

What do you mean the upcoming 30 days? For example, the upcoming 30 days of 2016-12-10 is 2017-01-09, you will merge shortdescription & Othertext for the date less than 2017-01-09 to one row where startdate 2016-12-10 shown in pivot table as follows. But if the row where the date is less than upcoming 30 days (eg,2016-12-30) will be include for another group(from 2016-12-30 to 2017-12-29 )? Could you share more details or post an overall example? And do you want to merge function for shortdescription & Othertext  (multiple rows) with a startdate the upcoming 30 days by creating calculated column or measure?



For merging shortdescription & Othertext  group by employee, you can use the CONCATENATEX Function to merge multiple row to one. The instance in this link describes the function clearly.

In addition, the Path is unsuitable for getting what you want. Path function displays Parent-Child Hierarchies in DAX, and returns a delimited text with the identifiers of all the parents to the current row, starting with the oldest or top most until current. This article help you better understanding the PATH function.

Best Regards,
Angelia

[mvananaken]

Hello,

Imagine the following table in PowerBI view. The underlying data comes from 2 related tables (employee and matchprofiles, 1:N)

Employee	MatchProfileID	Shortdescription	Othertext	Startdate
Employee A	1	Hello	Bonjour	2016-01-01
Employee A	2	Something	Something2	2016-01-01
Employee A	3	Yess..	No	2016-10-10
Employee A	4	Brr.	Brr22	2016-12-10

I’m looking for a merge function for shortdescription & Othertext (multiple rows) with a startdate the upcoming 30 days.

The result in a PowerBI table view should be something like this:

Employee	Mergedescription&Othertext
Employee A	Yess&No, Brr.&Brr22

-> one row!

I tried something with “PATH”, but got an error about not containing values because the fields I want to show aren't the table primary en secondary keys. The dataset is medium sized. 

Some ideas ar welcome! Thank you! 

[ImkeF]

First you have to filter your table (I used a parameter here, but ou could also use a formula if it should always be the last 30 days before today for example):

Table.SelectRows(#"Changed Type", each [Startdate] > DateParameter)

Then you add a column called "Description" where you concatenate the values of the 2 columns like this:

= Table.AddColumn(#"Filtered Rows", "Description", each [Shortdescription]&"&"&[Othertext])

Then you group on Employee ID and combine all rows of one Employee ID like this:

Table.Group(#"Added Custom", {"Employee"}, {{"All", each Text.Combine(_[Description], ", "), type table}})