Filter Context for Month is not working

[scpournara]

I wrote a fairly complex measure that is below. However, when I apply filter context, it does not work. Hoping someone can take a look and spot a suggestion?

Team Sales 2023A =
VAR MFG = SELECTEDVALUE(ColorClass[Color Classification])
VAR LEV =
SELECTEDVALUE ( 'Employee Mapping'[Position ID] )
VAR CAL =
CALCULATE (
SUM ( Sales1[Revenue] ),
ALL ( Sales1 ),
'Calendar1'[Year] = 2023,
PATHCONTAINS (
'Employee Mapping'[Path],
LEV
))
VAR MFG1 =
CALCULATE (
SUM ( Sales1[Revenue] ),
ALL ( Sales1 ),
'Calendar1'[Year] = 2023,
PATHCONTAINS (
'Employee Mapping'[Path],
LEV
),ColorClass[Color Classification]=MFG
)
VAR Sale =
CALCULATE (
SUM ( Sales1[Revenue] ),
'Calendar1'[Year] = 2023 )
VAR Fil = IF(ISFILTERED(ColorClass[Color Classification]),MFG1,CAL)

RETURN
IF (
Fil - Sale <= 0,
BLANK (),
Fil - Sale
)

 

[Matt Allington]

You will need to be more specific when you say “when I apply filter context”

 

[scpournara]

Apologize. The filter context if for Month. When I bring in "Month" as a filter from the Calendar table, the Filter on this Measure does not work. I do have another simple Measure for Revenue and when I apply it to that it does work.

 

[Matt Allington]

You haven't provided an image of the data model, so I will assume you have a calendar table joined to the sales table with a 1 to many relationship. My observations

• You are removing the filter from the sales table with ALL(Sales) then applying a filter on the calendar[year]. This is generally not good practice; you should be filtering the dimension tables (calendar in this case).
• I have never used the PATHCONTAINS function, but I assume it is behaving list a CONTAINS function to somehow apply a filter to the Employee Mapping table.

I think you can delete the ALL(Sales) altogether as it doesn't seem to add to the measures and it could be the cause of the problem given it conflicts with other filters inside your calculate function. My guess is that this is the problem, but I can't be sure. One test you can do is to replace the RETURN statement to simply return 'Sale'. If this works with the filter on month, then that would seem to confirm this is the issue.

 

[scpournara]

Thanks Matt for you time and effort to help me try and figure this out. I tried both your suggestions and was not able to get the measure to work. I had someone help write the Measure so I am lost. Everything works without the Month filters and I do think ALL (Sales1) is the issue. Been doing a lot of "trial and error" but nothing seems to work. Thanks for your effort. If you would like to continue to help, please let me know.

 

[RoryA]

the Filter on this Measure does not work

What exactly does that mean - what actually happens?

 

[Matt Allington]

I can help, but you need to provide more information. An image of the diagram view is a minimum

 

[scpournara]

Thanks Matt. I am including an image of the model and the measure. I tried the changes you recommended with removing ALL ( Sales1 ) and replacing the RETURN with Sales, but did not get the reults when I tested. I tried combinations of the changes as well. Thanks for reaching out.

 

[Matt Allington]

Can you share an image of the visual that shows it’s not working, preferably a table or matrix. You need to confirm which table each column used in the visual comes from