Change multiple lines of same event to one line??

[Creative_tree88]

Hi all - I have some data, which has multiple lines but relating to just one event.  Easier to show you what I have, and then what I need it to look like?

So...this is what I have:



As you can see, there are multiple instances of repeated event keys (these are events attended by a patient) where the patient has had multiple exams on the same event.  So, event 694837, the patient had 3 exams (MSKUH; MCARD; MAICA) - all relating to the same event.  However, the database kicks out each line individually, so we effectively have three lines per event in this example.  What I'd ideally like is this:

Each event key now has the different exams on the same line.  I don't really want to change the data source itself but rather use a clever measure, or way of manipulating the data, to be able to show on a table in this format i.e. all exams relating to same event, on one line.  It just makes it easier when trying to display certain exams and how they are grouped with others?

If I have to manipulate the data, then so be it, but I have no idea how to do so.  Would welcome any suggestions - as I say, would rather not mess with the data itself, but if I have to then I'll see how it works out with the rest of the data.  

Many thanks for your help in advance!

Kind regards

[Creative_tree88]

@DataInsights - thanks for your contribution.  Unfortunately your measure brings all exams onto each line of data.  Every line of data is the same - what I need is to just concatenate exams specific to their respective event key.  For example, there could be three exams per one unique event key so I want to be able to group those three exams and concatente them onto one line of the data.  Any ideas?

[DataInsights]

@Creative_tree88,

Would you be able to provide an example? The example of Event Key 694837 which has three exams seems to be concatenating properly.

[Creative_tree88]

@DataInsights - I've applied the measure to the entire table (CRIS Waits) and get the following:

It's certainly concatenating event key, but it's concatenating every single exam in the table against each event key.  I just want exams which are linked to a unique event key, to then be grouped onto a single line of data.  Not all exams will have multiple instances, in which case they will just be on the same line of data as the event key.

My problem is that when there are two or more exams per event key (event keys are unique so there will be two instances of the same event key) the exams are listed on a line of data.  I just want all exams to be grouped onto one line of data, relating to an instance of the event key.

I may be asking for the impossible.  I can do it in Excel, using multiple steps as follows:

=IF(a2<>a1,E2,G1 & " " & E2)     - this formula concatenates the event and exam (a=event key; E=Exam_

I have to make sure event key is sorted, followed by exam key before applying this formula.

I then apply this formula:

=IF(a2<>a3,COUNTIF($a$2:a2,a2),"")  - This counts the exams per event and I then simply delete any zeros as these will be the duplicated rows which I don't need to include.  A = event key

Many thanks @DataInsights 

[DataInsights]

@Creative_tree88,

Just to confirm, you're using the DAX in a measure, not a calculated column, right? You could try adding SUMMARIZE:

Exam Combo =
CONCATENATEX (
    SUMMARIZE ( 'CRIS Waits', 'CRIS Waits'[Event Key], 'CRIS Waits'[Exam] ),
    'CRIS Waits'[Exam],
    " "
)

If you could paste your sanitized data as a table, I'll be able to troubleshoot further.

[DataInsights]

@Creative_tree88,

Try this measure:

Exam = CONCATENATEX ( Table1, Table1[Exam], " " )