March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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?
Solved! Go to 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
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
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
90 | |
90 | |
66 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
70 | |
68 |