Which customers from the Central region did NOT buy Office Supplies in January

[gbarr12345]

Hi everyone,

I'm trying to find out which customers from the Central region did NOT buy Office Supplies in January using a DAX Measure.

I have code but the data results seem a bit off.

Can anybody be able to help me in finding a solution for my code please?

Thanks in advance!

Central Office Supplies 0 Sales =

VAR CentralCustomersWithOfficeSupplyPurchases =

    CALCULATETABLE (

        VALUES ( Orders[Customer Name] ),

        Orders[Region] = "Central",

        Orders[Product Category] = "Office Supplies",

        Orders[Order Date] >= DATE(2015, 1, 1),

        Orders[Order Date] <= DATE(2015, 1, 31)

    )

VAR AllCentralCustomers =

    CALCULATETABLE (

        VALUES ( Orders[Customer Name] ),

        Orders[Region] = "Central"

    )

RETURN

EXCEPT ( AllCentralCustomers, CentralCustomersWithOfficeSupplyPurchases )

[gbarr12345]

That worked thanks but I'm looking for the users from the central region that haven't purchased Office Supplies in the month of January. Can you alter the above code to show the central region and the month of january also?

[Ashish_Mathur]

Study my solution carefully.  That is exactly what it does.

[gbarr12345]

Oh yes I see that but I'm wondering is there a way to incorporate it into the DAX measure without the need for using Slicers?

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[gbarr12345]

Hi Ashish,

I copied your measures and I got no error but it still seems the incorrect numbers are appearing for me which they don't seem to for you. My number is bigger than yours as I had more data but it still seems incorrect. 

Can you see where I went wrong possible? My customer name field is in my orders table whereas you created a customers table it seems which is fine too but that's the only difference I can see.

Screenshots below are in the order of how I did things:

[Ashish_Mathur]

Based on screenshots, i really cannot help you.

[gbarr12345]

No problem at all. Thanks for the help Ashish much appreciated!

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[gbarr12345]

Customer Name	Product Category	Region	Order Date	Sales
Kara Allison	Furniture	Central	27/01/2015	10.23
Max Small	Technology	Central	16/01/2015	85.79
Patricia Cole Blair	Technology	Central	16/01/2015	192.3
Patricia Cole Blair	Technology	Central	16/01/2015	748.1
Roger Schwartz	Office Supplies	Central	3/01/2015	22.82
Roger Schwartz	Technology	Central	3/01/2015	188.66
Edgar McKenzie	Office Supplies	Central	27/01/2015	73.18
Edna Monroe Talley	Office Supplies	Central	30/01/2015	18.75
Edna Monroe Talley	Furniture	Central	30/01/2015	188.51
Peggy Chan	Office Supplies	Central	30/01/2015	31.18
Eva Silverman	Office Supplies	Central	17/01/2015	285.87
Joel Huffman	Office Supplies	Central	17/01/2015	20.37
Howard Rogers	Furniture	Central	7/01/2015	205.98
Allison Kirby	Furniture	Central	16/01/2015	832.14
Albert Frost	Office Supplies	Central	2/01/2015	485.01
Jeffrey Mueller	Technology	Central	30/01/2015	336.64
Billie Fowler	Furniture	Central	5/01/2015	19.97
Billie Fowler	Technology	Central	5/01/2015	660.03
Jennifer Siegel	Furniture	Central	10/01/2015	49.78
Jennifer Siegel	Office Supplies	Central	10/01/2015	16.62
Carol Sherrill	Furniture	Central	4/01/2015	346.52
Marion Wilcox	Office Supplies	Central	4/01/2015	499.31
Jane Shah	Office Supplies	Central	2/01/2015	41.6
Marjorie Owens	Office Supplies	Central	26/01/2015	27.76
Carmen McPherson	Technology	Central	2/01/2015	3855.28
Russell Chan	Office Supplies	Central	4/01/2015	2560.55
Eileen Fletcher	Office Supplies	Central	15/01/2015	58.95
Eileen Fletcher	Technology	Central	15/01/2015	3672.89
Jeremy Pratt	Office Supplies	Central	15/01/2015	11.74
Eva Simpson	Furniture	Central	31/01/2015	1145.6
April Bowers	Furniture	Central	31/01/2015	1634.67
Pauline Denton	Furniture	Central	24/01/2015	1774.5
Pauline Denton	Technology	Central	24/01/2015	1054.69
Arlene Gibbons	Furniture	Central	26/01/2015	119.29
Kristina Collier	Office Supplies	Central	26/01/2015	3.08
James Hunter	Technology	Central	7/01/2015	79.02
Wesley Tate	Office Supplies	Central	15/01/2015	447.89
Wesley Tate	Technology	Central	21/01/2015	1420.84
Crystal Floyd	Office Supplies	Central	15/01/2015	111.97
Crystal Floyd	Technology	Central	21/01/2015	355.21
Rebecca Lindsey	Office Supplies	Central	1/01/2015	36.58
Rodney Field	Office Supplies	Central	30/01/2015	123.03
Rodney Field	Furniture	Central	30/01/2015	431.84
Rhonda Schroeder	Office Supplies	Central	23/01/2015	37.89
Sherri P Stephens	Office Supplies	Central	9/01/2015	10.19
Miriam Greenberg	Office Supplies	Central	3/01/2015	131.34
Derek Jernigan	Furniture	Central	6/01/2015	6968.9
Stacey Lucas	Office Supplies	Central	6/01/2015	100.99
Stacey Lucas	Technology	Central	6/01/2015	142.06
Suzanne Cochran	Office Supplies	Central	6/01/2015	363.74
Suzanne Cochran	Office Supplies	Central	6/01/2015	15.93
Suzanne Cochran	Office Supplies	Central	6/01/2015	21.28
Sherri F Vogel	Furniture	Central	10/01/2015	551.51
Sherri F Vogel	Office Supplies	Central	10/01/2015	142.79
Marianne Carey	Furniture	Central	29/01/2015	260.66
Mary Hewitt	Furniture	Central	3/01/2015	302.34
Philip Hawkins	Technology	Central	10/01/2015	334.44
Melinda Rogers	Furniture	Central	5/01/2015	248.84
Laurie Howe	Furniture	Central	31/01/2015	1635.38
Laurie Howe	Office Supplies	Central	31/01/2015	58.48
Laurie Howe	Office Supplies	Central	31/01/2015	40.05
Eddie Walker	Technology	Central	4/01/2015	807
Bobby Powell	Technology	Central	5/01/2015	55.25
Bobby Powell	Technology	Central	5/01/2015	551.22
Helen Dickerson	Office Supplies	Central	26/01/2015	577.75
James Davenport	Office Supplies	Central	29/01/2015	59.4
James Davenport	Office Supplies	Central	29/01/2015	66.46
Monica Harvey	Technology	Central	27/01/2015	1610.84
Nancy Holden	Office Supplies	Central	24/01/2015	76.87
Glen Robertson	Office Supplies	Central	6/01/2015	558.41
Glen Robertson	Office Supplies	Central	6/01/2015	290.22
Arlene Wiggins Dalton	Office Supplies	Central	22/01/2015	42.56
Nicholas Wallace	Technology	Central	21/01/2015	414.91
Yvonne Collier	Office Supplies	Central	22/01/2015	140.7
Troy Casey	Office Supplies	Central	3/01/2015	15.95
Karl Knowles	Office Supplies	Central	9/01/2015	30.1
Danielle P Rao	Office Supplies	Central	17/01/2015	448.47
Betsy Puckett	Furniture	Central	5/01/2015	1000.26
Betsy Puckett	Furniture	Central	5/01/2015	2184.99
Betsy Puckett	Technology	Central	5/01/2015	1191.2
Betsy Puckett	Technology	Central	7/01/2015	165.71
Becky Farmer	Technology	Central	12/01/2015	31.96

The expected result is that it will show what users that come from the Central region did not purchase Office equipment in the month of January.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.