Filter put on measure returning wrong result?

FlashFire

New Member
Joined
Jun 16, 2016
Messages
10
So I have this code:

Code:
KPI 2016 = SUMX(
    FILTER(
        ALL('Data Warehouse'[Sale.Invoice Date],'Data Warehouse'[EXT AMOUNT],'Data Warehouse'[Customer.Customer Name]),
        YEAR([Sale.Invoice Date]) = 2016 &&
        MONTH([Sale.Invoice Date]) <= 'LTProd Data Warehouse'[Current Last Month] &&
        MONTH([Sale.Invoice Date]) >= 'LTProd Data Warehouse'[Current First Month] &&
        IF( EXACT([Current Customer],"ALL"),
            TRUE(),
            EXACT([Customer.Customer Name],[Current Customer])
        )
    ),
[Profit])

Everything works but the IF function.
The if function is suppose to check if the measure with the value "all" or the name of the current customer, and then apply that as a filter. For what ever reason [Current Customer] doesn't return the current customer, as if the IF is applying its own filter to the measure, messing up the data.

The formula for the current customer measure is:

Code:
Current Customer = IF(HASONEVALUE('Data Warehouse'[Customer.Customer Name]),
    LASTNONBLANK('Data Warehouse'[Customer.Customer Name],TRUE()),
    "ALL")

Am I right in my conclusion? and if so, how do I make so it doesn't filter the measure when it tries to get data from it?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
So, I would be doing some guessing and checking. Things I would look at:
* Do you need a RELATED() around the [Customer.Customer Name] ?
* Do you need to throw a CALCULATE() anywhere -- to convert the row context from the sumx to a filter context?
* Do things magically get better if you pull the expression from [Current Customer] directly into the KPI measure?
 
Upvote 0
So, I would be doing some guessing and checking. Things I would look at:
* Do you need a RELATED() around the [Customer.Customer Name] ?
* Do you need to throw a CALCULATE() anywhere -- to convert the row context from the sumx to a filter context?
* Do things magically get better if you pull the expression from [Current Customer] directly into the KPI measure?

Using RELATED creates the error:

The column 'Data Warehouse[Customer.Customer Name]' either doesn't exist or doesn't have a relationship to any table available in the current context.

When I put the formula for [Current Customer] directly into the Measure, the same results happen. The ALL removes the filters that are created by slicers on Customer.Customer Name on [Current Customers] when I call it in the filter.

Tried these:
Code:
KPI 2016 = 
SUMX(
FILTER(
	FILTER(
		ALL('Data Warehouse'[Sale.Invoice Date],'Data Warehouse'[EXT AMOUNT],'Data Warehouse'[Customer.Customer Name]),
		YEAR([Sale.Invoice Date]) = 2016 &&
		MONTH([Sale.Invoice Date]) <= Data Warehouse'[Current Last Month] &&
		MONTH([Sale.Invoice Date]) >= 'Data Warehouse'[Current First Month] --&&
	),
IF( EXACT('Data Warehouse'[Current Customer],"ALL"),
	TRUE(),
	[Customer.Customer Name] = 'Data Warehouse'[Current Customer]
)
),[EXT AMOUNT])
Code:
KPI 2016 = 
CALCULATE(
	SUMX(
		FILTER(
			ALL('Data Warehouse'[Sale.Invoice Date],'Data Warehouse'[EXT AMOUNT],'Data Warehouse'[Customer.Customer Name]),
			YEAR([Sale.Invoice Date]) = 2016 &&
			MONTH([Sale.Invoice Date]) <= 'Data Warehouse'[Current Last Month] &&
			MONTH([Sale.Invoice Date]) >= 'Data Warehouse'[Current First Month]
		),
		[EXT AMOUNT]
	),
	'Data Warehouse'[Customer.Customer Name]
)
 
Upvote 0
Tried it, it gives the error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

My code:

Code:
KPI 2016 = SUMX(
    FILTER(
        ALL('Data Warehouse'),
        YEAR([Sale.Invoice Date]) = 2016 &&
        MONTH([Sale.Invoice Date]) <= 'Data Warehouse'[Current Last Month] &&
        MONTH([Sale.Invoice Date]) >= 'Data Warehouse'[Current First Month] &&
        'Data Warehouse'[Customer.Customer Name] = EARLIER('Data Warehouse'[Customer.Customer Name])
    ),
[EXT AMOUNT])
 
Upvote 0
So it is because the Current Column is using the context of when I try to use it, Because when I changed the code to:

Current Column = "Some customer name"

Everything worked as expected.

Would there be some way I can access [Current Column] without using filters in the current context? Or adjust the code for [Current Column] so the only thing that effects it is Slicers/Charts?

Or maybe I'm going about this whole thing the wrong way?
 
Upvote 0
Re-reading this, it is not totally clear to me what you are hoping to achieve. If you just leave that clause out what is "wrong" with the output?
 
Upvote 0
I have a KPI that calculates the total amount earned where year = x and for month range y-z. I wanted to make it so that when you select one of the customers, it makes the data display only for that customer. Right now nothing happens when you go from selecting all the data to an individual customer.
 
Upvote 0
For the most part... slicing on a particular dimension should "just work", unless you are missing a relationship (say, between a sales and customer table).

Looking at your code again:
Code:
KPI 2016 = SUMX(
    FILTER(
        ALL('Data Warehouse'[Sale.Invoice Date],'Data Warehouse'[EXT AMOUNT],'Data Warehouse'[Customer.Customer Name]),
        YEAR([Sale.Invoice Date]) = 2016 &&
        MONTH([Sale.Invoice Date]) <= 'LTProd Data Warehouse'[Current Last Month] &&
        MONTH([Sale.Invoice Date]) >= 'LTProd Data Warehouse'[Current First Month] &&
        IF( EXACT([Current Customer],"ALL"),
            TRUE(),
            EXACT([Customer.Customer Name],[Current Customer])
        )
    ),
[Profit])

I'm skeptical of the ALL('Data Warehouse'[Customer.Customer Name])) ... as... ya... if you slice on 1 customer, that ALL() is going to remove that filter and still show "everybody". Maybe just remove that column from your ALL() ?
 
Upvote 0
No difference. It didn't slice slice the data when I clicked a customer.

Code:
KPI 2016 = SUMX(
		FILTER(
				ALL('Data Warehouse'[Sale.Invoice Date],'Data Warehouse'[EXT AMOUNT]),
				YEAR([Sale.Invoice Date]) = 2016 &&
				MONTH([Sale.Invoice Date]) <= 'Data Warehouse'[Current Last Month] &&
				MONTH([Sale.Invoice Date]) >= 'Data Warehouse'[Current First Month]
			),
[EXT AMOUNT])
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top