Creating Conditional Columns Filtered Within Rows

[Darko_Giac]

Hi all,

I'm currently working with a fairly large data set (for which I included a sample), and am having some issues developing a flags that will filter within rows.

For example, I'm currently analyzing test data that requires students take a test at one time, then come back to take the test again at another, later date. So Student 101 would have two entries across a few variables:

However, there a couple of conditions I'm trying to build in:

1). The re-test can't have happened two weeks BEFORE the initial test, or four weeks AFTER.

2). I'm also looking to flag discrepencies within IDs and TestStart and EndTimes (for example, both test start and test end should occur on the same day, as the test duration is only a few hours).

So ID101 would have a "Invalid Case" flag, as more than four weeks have elapsed between test and retest

I have a feeling this is something that could be more effectively done in power pivot, but just not quite sure how to get there.

(The data in the attached workbook is also connected to powerpivot)

Test Retest Workbook

Currently using Office 365 ProPlus.

Any help is greatly appreciated, thanks in advance!

Darko

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution workbook from here.

Hope this helps.

[Darko_Giac]

Thanks for the solution @Ashish_Mathur!

The time/date flag worked really well.

I'm wondering whether there is a way to create a gender flag (either through a measure or calculate column) that will flag mismatches in gender between the same ID. 

For example,

My data are set up so that each ID has two entries, and ID101 for instance might have a gender of Male on one row, but Female on the next row. 

Would there be a way to flag those discrepancies?

See attached a workbook: Workbook_11.24.2018

Thanks again for the help!

Darko

[Ashish_Mathur]

Hi,

You are welcome.  I receive a message saying "Download not available"