4 Replies Latest reply on Feb 29, 2016 8:47 AM by Bill Lyons

    Elevating LOD Exclude above Dimension Filters

    Santiago Perez

      Very often, i find myself wanting to use the exclude function but being foiled by the fact that dimension filters are executed first.

      Untitled.png

      Scenario: I'm computing a percentile rank for all companies in my database

      RANK_PERCENTILE(avg(Name[community_impact_area_points__c])

       

      This works fine, until I try to filter to a single company, when the calculation becomes a rank of 1 out of 1.

      My first instinct is to exclude

      RANK_PERCENTILE(avg({exclude [Name]: avg([community_impact_area_points__c])}))

      But the 1 out of 1 problem persists. Now I could try to figure out how to use fixed , but my data model is quite complex so figuring out all the dimensions I would fix by is so much more complex than just excluding what I know I want it to ignore.

       

      There are dozens of threads that tell you how to downgrade fixed by adding it to the context, so that the dimension filters affect it. But is there a way of promoting include/exclude above dimension filters? If there is not, then is there a simple way of understanding how to substitute fixed into my use case? I would offer a twx, but that takes days to create in my case.