Forum Discussion

catfood49's avatar
catfood49
Frequent Visitor
8 years ago

Measure for Inverse of Filter?

I am analysing Contacts who have visited certain Pages (Touchpoints) in a website. Of all the pages of the site, I have 3 that I want to analyse in a filter. These 3 pages are in a separate table. From the Filter of pages I want to create the following measures:

 

  1. Contacts who have visited pages selected by the Filter
  2. Contacts who have visited pages NOT selected by the filter

The table structure is straightforward:

 

 

I have managed to create the Measure 'Visited Selected' which is correctly showing contacts who have Events for the selected filters (A) & (C) as shown below, however I can't seem to get the Measure for 'Visited Non-Selected' working.

 

 

 

The code I have written for the measure to count contacts who have visited '(B) Player' is below however it is returning 0:

 

Visited Non-Selected = 

var selected = values( '_Filter Touchpoint'[Touchpoint] )
var allTouchpoint = all( '_Filter Touchpoint'[Touchpoint] )
var notSelected = EXCEPT( allTouchpoint , selected )

var distinctContact = GROUPBY(
        ADDCOLUMNS(
                filter(
                        GROUPBY(  
                                Event               
                                , Event[Contact Key]
                                , Event[Touchpoint Key]                                
                        )
                        , Event[Touchpoint Key] in notSelected
                )
                , "Event Count" , 1
        )
        , Event[Contact Key]
        , "Total Events" , SUMX( CURRENTGROUP() , [Event Count] )
)

return if( SUMX( distinctContact , [Total Events] ) = [Filter Count] , SUMX( distinctContact , [Total Events] ) , 0 )

Either this code needs some tweaking or a need a very different approach. Can anyone help?

 

Thanks