cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LAURENTW
New Member

Measure with filters on related table does not react to slicers

I have a Calendar Table, which include as a column the First day of the month, and a Sales Order Table, which is related to it by the Sales Order Created Date. The Sales Order Table is also related to a Customers table, via a Customer Account field
I have created a measure in the Calendar table, to calculate cumulative order values from start of month, each month
This works fine, but if select certain customers from a slicers, this does not impact the result. I would have expected the slicer selection to filter the Sales Order table, and the dates filters in the measure to be applied on top


Month Cumulative Order Intake =
CALCULATE(SUMX('Sales Orders Totals','Sales Orders Totals'[Total Ordered Value])
,FILTER('Sales Orders Totals','Sales Orders Totals'[Created Date]>='Calendar'[Month Start]
&& 'Sales Orders Totals'[Created Date]<='Calendar'[Date]))

 

1 ACCEPTED SOLUTION

Hi Pete

Many thanks, that little hint actually help a lot... I duplicated the Sales Order table as unrelated to Calendar, used similar, and it now behave as expected

Month Cumulative Order Intake =
var MonthStart =MIN('Calendar'[Month Start])
Var MonthLast = MAX('Calendar'[Date])
RETURN
CALCULATE(SUMX('Sales Orders Totals (Date Unrelated)','Sales Orders Totals (Date Unrelated)'[Total Ordered Value])
,FILTER('Sales Orders Totals (Date Unrelated)','Sales Orders Totals (Date Unrelated)'[Created Date]>=MonthStart
&& 'Sales Orders Totals (Date Unrelated)'[Created Date]<=MonthLast))

View solution in original post

5 REPLIES 5
Fowmy
Super User IV
Super User IV

@LAURENTW 

Can you see if this works?

Month Cumulative Order Intake =
CALCULATE (
    SUM( 'Sales Orders Totals'[Total Ordered Value] ),
    FILTER (
        ALL('Sales Orders Totals'[Created Date]),
        'Sales Orders Totals'[Created Date] >= max('Calendar'[Month Start])
            && 'Sales Orders Totals'[Created Date] <= max('Calendar'[Date])
    )
)



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank you for the help. That did not work, but it looks like the issue was with having relationship between Calendar table and Order table, and at same time filtering. Removed relationship and good now

BA_Pete
Super User II
Super User II

Hi @LAURENTW ,

 

Can you post a picture of your model please?

At first glance, it looks like a relationship setup issue but want to be sure.

 

Thanks,

 

Pete

Hi Pete

Many thanks, that little hint actually help a lot... I duplicated the Sales Order table as unrelated to Calendar, used similar, and it now behave as expected

Month Cumulative Order Intake =
var MonthStart =MIN('Calendar'[Month Start])
Var MonthLast = MAX('Calendar'[Date])
RETURN
CALCULATE(SUMX('Sales Orders Totals (Date Unrelated)','Sales Orders Totals (Date Unrelated)'[Total Ordered Value])
,FILTER('Sales Orders Totals (Date Unrelated)','Sales Orders Totals (Date Unrelated)'[Created Date]>=MonthStart
&& 'Sales Orders Totals (Date Unrelated)'[Created Date]<=MonthLast))

View solution in original post

@LAURENTW ,

 

Great news, happy to help. Also happy to receive credit where due 😉

 

Pete

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors