Assigning a value to a custom column using EARLIER

[Anonymous]

I'm trying to create a custom column indicating if my customers have purchased Computers, Laptops or Both.  I was able to assign the value of Computer and Laptop at the row level using an If Then statement but that doesn't take into account that I have multiple rows for each company.  I'd like to evaluate the rows filtering on [Company Name] = EARLIER[Company Name]  but can't get the DAX query correct.  Maybe that's not the way to go?  

Ultimately I would like to create a visual showing the numbers of customers filtering on Computer, Laptop and Both.

Sample Data:

Company Name	Order Quantity	Item
AA	1	Computer
AA	2	Laptop
AA	2	Printer
AA	1	Computer
BB	4	Computer
BB	5	Laptop
BB	6	Printer
BB	1	Computer
CC	13	Printer
CC	15	Computer
CC	2	Computer
CC	1	Printer
DD	4	Computer
DD	5	Laptop
DD	6	Printer
DD	1	Computer
EE	13	Printer
EE	6	Laptop
EE	2	Laptop
EE	1	Printer

[m3tr01d · Accepted solution]

hi @Anonymous 

I think that first, you should make a Company dimension and add the calculated columns there. 
The main reason being that your Order table (Fact Table) migh have milions of rows and that extra columns might take space in your model. Also the DAX expresion will be easier.

Company dimension
To have a company dimension, you need to have a table where you have one unique row per company. If you already have a SQL datasource that has this information, you can use SQL to build this table. 
If you don't have this information, you could also build a calculated table using

CompanyDim = ALL( 'Order'[Company Name] )

Once you have your dimension, you'll need to create the relationship between your Fact table and the dimension. It should be Many to One relationship.

Once you have that, you can create this base measure if you don't have it :

Order_Qty = SUM( 'Order'[Quantity] )

Now, we create the calculated column in the Company Dimension

Company_Classification = 
VAR _Qty_Computer = 
CALCULATE(
	[Order_Qty],
	'Order'[Item] = "Computer"
)

VAR _Qty_Laptop = 
CALCULATE(
	[Order_Qty],
	'Order'[Item] = "Laptop"
)

RETURN
SWITCH(
	TRUE(),
	_Qty_Computer>0 && _Qty_Laptop>0, "Both Items",			--Both >0 means that Company bought both items
	_Qty_Computer>0, "Computer",
	_Qty_Laptop>0, "Laptop",
	"Other"
)

Then, you can use this Calculated Column in row/column of a matrix with Order_Qty on Values.

Hope this helps

[Anonymous]

@m3tr01d  Sweet!  That worked!!  

I was about to post that I didn't get any "Both Items" but as I was writing the steps I took, I realized that on the first go-round I created the Company Classification column in the Sales Data table.  When I re-read and correctly created it in the Company Dim table it worked perfectly.  Thank you very much for the help!

[bcdobbs]

Hi @Anonymous,

If I understand your requirement I think you want to do something like this:

Buyer Type = 
VAR _Items = 
    CALCULATETABLE (
        VALUES( Orders[Item] ),
        ALLEXCEPT ( Orders, Orders[Company Name] ),
        Orders[Item] IN { "Computer", "Laptop" }
    )

VAR _Result =
    IF( COUNTROWS (_Items) = 1, _Items, "Both" )

RETURN _Result

Haven't needed to use it here but in general EARLIER is a legacy function. You're much better to store a value from a current iteration in a variable before creating a new row context. 

[bcdobbs]

@m3tr01d 's suggestion to move it into a star shema with a company dimension is really good one!