how to combine values of 2 tables and compare dates based on ID

[vx99]

Hello,

I would like to visualize "mType" after and before a certain event which can be filtered by selecting the option (after or before). For this, I wanted to compare all values of "measurementDate" and "EventDate" belonging to a "pid". I don't know if it can be visualized without creating a new table (I just started working with power bi), but if a table is required, it probably needs to result in something like this:

pid  mType  measurementDate  Event AfterEvent

1        A                 10/10/2015               X         false

1        A                 10/10/2015               Y         true

1        A                 13/11/2015               X         false

1        A                 13/11/2015               Y         true

3        D                 11/08/2016               Z         true

1        B                 12/01/2016                X       false    

1        B                 12/01/2016                Z      false

2        C                 21/10/2017               X       true

2        D                 06/02/2018              X       false

Therefore, I need to find a way to somehow combine all "mType" of one "pid" with all events of one pid and then calculate the date difference based on the following 2 tables:

pid   mType  measurementDate

1        A                  10/10/2015

1        A                 13/11/2015

3        D                 11/08/2016

1        B                 12/01/2016

2        C                 21/10/2017

2        D                 06/02/2018

pid Event EventDate

1     X      01/10/2015

1     Y      28/12/2015

2     X      10/11/2017

3     Z     05/09/2016

These 2 tables are connected via another table which contains all unique pid values.

I have tried different things but so far nothing worked. Do you have ideas on how to approach this?

Thanks in advance, I really appreciate the help!

[tamerj1]

Hi @vx99 

how do the original tables look like?

[vx99]

Hi, the resulting table has to be created based on these 2 tables:

(Unfortunately, I have only these two dummy tables as the original ones are confidential.)

pid   mType  measurementDate

1        A                  10/10/2015

1        A                 13/11/2015

3        D                 11/08/2016

1        B                 12/01/2016

2        C                 21/10/2017

2        D                 06/02/2018

pid Event EventDate

1     X      01/10/2015

1     Y      28/12/2015

2     X      10/11/2017

3     Z     05/09/2016

[tamerj1]

Hi @vx99 

place the pid column from the dim table in a table visual followed by table1[mType], table1[measurementDate] and table1[Event] then place the following measure 

AfterEvent = SELECTEDVALUE ( table2[EventDate] ) > SELECTEDVALUE ( table1[measurementDate] )

[vx99]

Hi tamerj1,

Thank you for your help! 🙂 However, it seems like it is creating all possible combinations of pid and mType and the true/false statements don't seem quite right, unfortunately (e.g. it says true even though the event date is before the measurement date).

[tamerj1]

@vx99 

"However, it seems like it is creating all possible combinations of pid and mType" this exactly how you presented your expected results. 
"the true/false statements don't seem quite right" add the event column to the right side table and it should gove correct results. 

[vx99]

But in my expected results you can see that e.g. pid 2 is never associated with mtype A or B, while it is the case in the output.

[tamerj1]

@vx99 

Yes you are right. That happens with boolean condition measures. Please try

AfterEvent =

VAR Date2 = SELECTEDVALUE ( table2[EventDate] )

VAR  Date1 = SELECTEDVALUE ( table1[measurementDate] )

RETURN

IF ( 

Date1 <> BLANK ( ) && Date2 <> BLANK ( ),

Date2 > Date1

)

[vx99]

That didn't work either, sadly. 

However, I tried out using DATEDIFF and checked if the values are positive, negative or blank resulting in the correct visualization!

Thanks for your help anyways!