ALL returning irrelevant rows from dimension

[Anonymous]

Hi All, 

I need your help please. I've got a model where my fact table joins to various dimensions, see below for detail. In my report page I'm looking at the sum of the Value field by Range in a given Campaign, the only filter on the page is the CampaignName, and I've got a table with Range and Value. I can only see the Ranges that are relevant for that Campaign and the relevant Values for the Campaign. Then I tried to add a calculated column to tell me the overall Value for the entire campaign across all Ranges that are valid for the date range of the campaign so I can then put the sum of the Range against that total. I tried using the following: 

Subtotal = CALCULATE(sum('FactTable'[Value]),ALL('FactTable') --> this returned every single Range that ever existed in my RangeDimension table and ignored the date filter on the campaign as well, why?

Subtotal = CALCULATE(sum('FactTable'[Value]),ALLEXCEPT('CampaignCalendar'[CampaignName])) --> this got rid of the irrelevant Ranges but it's just returning the sum of the Value for each Range, why? 

As soon as I try and include an ALLEXCEPT on any of the fields in my RangeDimension I get all the irrelevant ranges again popping up. I do not have both directional filter on any of the joins, they are all straight forward many to one from my fact to my dimensions. 

Can anyone advise please?

• Range Dimension: contains each Range and its attributes and their valid from and valid to date, these are campaign based time periods
• Fact table: shows the actual bookings value for each range on a given day on a given branch 
• Campaign Calendar: shows the campaign details of each date 
• Calendar Detail: is needed for other calculations irrelevant to this one but is a calendar detail table based on a financial calendar
• Branch table: standard branch attributes table to show the attributes of locations

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understand , maybe you want to calculate the total value based on Date Slicer and page filter , right?

In addition, ALL() will ignore any filters that might have been applied. Refer to  this article  for more information.

You could use the following formula or  take a look at my pbix file  .

DateSlicer =
CALENDAR (
    MIN ( RangeDimensionTable[RangeDateFrom] ),
    MAX ( RangeDimensionTable[RangeDateTo] )
)

sum =
VAR _filter =
    SELECTEDVALUE ( 'CamparingCalender'[CamparingName] )
VAR _min =
    MIN ( DateSlicer[Date] )
VAR _max =
    MAX ( DateSlicer[Date] )
RETURN
    CALCULATE (
        SUM ( FactTable[Value] ),
        'CamparingCalender'[CamparingName] = _filter,
        FILTER (
            RangeDimensionTable,
            RangeDimensionTable[RangeDateFrom] >= _min
                && RangeDimensionTable[RangeDateTo] <= _max
        ),
        ALLEXCEPT ( FactTable, FactTable[RangeDimensionSkey] )
)

filterCalendarID =
IF (
    MAX ( FactTable[CalendarID] ) IN VALUES ( CamparingCalender[CalendarID] ),
    1,
    0
)

And apply the filterCalendarID to visual filter(set as “1”) and ComparingName from CamparingCalender table to page filter.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Greg_Deckler]

@Anonymous - Having a bit of trouble following, sample data and expected output. ALL will return all rows.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.