Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

ALL returning irrelevant rows from dimension

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

 

DK_PBC_0-1599645949577.png

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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.

 

9.9.5.1.png

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

View solution in original post

2 REPLIES 2
v-eqin-msft
Community Support
Community Support

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.

 

9.9.5.1.png

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
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.