Forum Discussion

ispajic's avatar
ispajic
Icon for Helper I rankHelper I
8 years ago

Select an end date column based on content ISBLANK

Hi guys,

I have 2 data sources.

Click times table, that tracks when a certain slide was opened:

User IDRecord clickSlide 1 clickSlide 2 clickSlide 3 clickSlide 4 clickFinish click
84559301-01-09 6:0001-01-09 6:0101-01-09 6:0201-01-09 6:03 01-01-09 6:04
444539201-01-09 7:0001-01-09 7:0101-01-09 7:0301-01-09 7:07 01-01-09 7:08

 

Dummy value table, that collects responses of users by time:

User IDTimestampReaction
84559301-01-09 6:001
84559301-01-09 6:012
84559301-01-09 6:022
84559301-01-09 6:033
444539201-01-09 7:001

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.

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar

    Hi ispajic,

     

    I still a little confused about your logic.


    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.

     

    However, for your this scenario, you could try with RELATED function since you have the relationship of the two tables.

     

    If it is convenient, could you share your desired output, so that we can help further investigate on it?

     

    Best Regards,

    Cherry