Forum Discussion
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:
- Contacts who have visited pages selected by the Filter
- 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
1 Reply
- Greg_DecklerCommunity Champion
Take a look at my Inverse Aggregator Quick Measure here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266