Hi guys,
I have 2 data sources.
Click times table, that tracks when a certain slide was opened:
| User ID | Record click | Slide 1 click | Slide 2 click | Slide 3 click | Slide 4 click | Finish click |
| 845593 | 01-01-09 6:00 | 01-01-09 6:01 | 01-01-09 6:02 | 01-01-09 6:03 | | 01-01-09 6:04 |
| 4445392 | 01-01-09 7:00 | 01-01-09 7:01 | 01-01-09 7:03 | 01-01-09 7:07 | | 01-01-09 7:08 |
Dummy value table, that collects responses of users by time:
| User ID | Timestamp | Reaction |
| 845593 | 01-01-09 6:00 | 1 |
| 845593 | 01-01-09 6:01 | 2 |
| 845593 | 01-01-09 6:02 | 2 |
| 845593 | 01-01-09 6:03 | 3 |
| 4445392 | 01-01-09 7:00 | 1 |
Connections are currently set up between User ID's, as 1:many, both ways.
What I've done so far is to create a tracker for, let's say Slide 1, to show me how each of the Users has reacted on Slide 1, i.e. in period Click 1 to Click 2:
Reaction Slide 1 =
CALCULATE (
AVERAGE ( 'Dummy Value'[Reaction] ),
FILTER (
'Dummy Value',
'Dummy Value'[Timestamp] >= MIN( 'Click times'[Slide 1 click] )
&& 'Dummy Value'[Timestamp] <= MAX ( 'Click times'[Slide 2 click] )
)
)
However, not all of my slideshows are born equal. Some have only 3 slides, some more. So I'm wondering what the best approach would be for measuring the reactions for Slide 3, if my Slide 4 click column is empty. How would I say: IF Slide 4 click ISBLANK, select Finish click instead. I can't do it right now, because when I select ISBLANK in an IF statement, none of the columns from Click times are offered, probably due to the fact that my measure is located in the Dummy Value table and there is a datamodel problem.
