Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gbarr12345
Post Patron
Post Patron

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

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(201511),
        Orders[Order Date] <= DATE(2015131)
    )
VAR AllCentralCustomers =
    CALCULATETABLE (
        VALUES ( Orders[Customer Name] ),
        Orders[Region] = "Central"
    )
RETURN
EXCEPT ( AllCentralCustomersCentralCustomersWithOfficeSupplyPurchases )
10 REPLIES 10
gbarr12345
Post Patron
Post Patron

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?

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1714463126427.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

 

gbarr12345_0-1714511977743.png

 

gbarr12345_1-1714511995748.png

 

gbarr12345_2-1714512025428.png

 

Based on screenshots, i really cannot help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Ashish_Mathur
Super User
Super User

Hi,

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

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Customer NameProduct CategoryRegionOrder DateSales
Kara AllisonFurnitureCentral27/01/201510.23
Max SmallTechnologyCentral16/01/201585.79
Patricia Cole BlairTechnologyCentral16/01/2015192.3
Patricia Cole BlairTechnologyCentral16/01/2015748.1
Roger SchwartzOffice SuppliesCentral3/01/201522.82
Roger SchwartzTechnologyCentral3/01/2015188.66
Edgar McKenzieOffice SuppliesCentral27/01/201573.18
Edna Monroe TalleyOffice SuppliesCentral30/01/201518.75
Edna Monroe TalleyFurnitureCentral30/01/2015188.51
Peggy ChanOffice SuppliesCentral30/01/201531.18
Eva SilvermanOffice SuppliesCentral17/01/2015285.87
Joel HuffmanOffice SuppliesCentral17/01/201520.37
Howard RogersFurnitureCentral7/01/2015205.98
Allison KirbyFurnitureCentral16/01/2015832.14
Albert FrostOffice SuppliesCentral2/01/2015485.01
Jeffrey MuellerTechnologyCentral30/01/2015336.64
Billie FowlerFurnitureCentral5/01/201519.97
Billie FowlerTechnologyCentral5/01/2015660.03
Jennifer SiegelFurnitureCentral10/01/201549.78
Jennifer SiegelOffice SuppliesCentral10/01/201516.62
Carol SherrillFurnitureCentral4/01/2015346.52
Marion WilcoxOffice SuppliesCentral4/01/2015499.31
Jane ShahOffice SuppliesCentral2/01/201541.6
Marjorie OwensOffice SuppliesCentral26/01/201527.76
Carmen McPhersonTechnologyCentral2/01/20153855.28
Russell ChanOffice SuppliesCentral4/01/20152560.55
Eileen FletcherOffice SuppliesCentral15/01/201558.95
Eileen FletcherTechnologyCentral15/01/20153672.89
Jeremy PrattOffice SuppliesCentral15/01/201511.74
Eva SimpsonFurnitureCentral31/01/20151145.6
April BowersFurnitureCentral31/01/20151634.67
Pauline DentonFurnitureCentral24/01/20151774.5
Pauline DentonTechnologyCentral24/01/20151054.69
Arlene GibbonsFurnitureCentral26/01/2015119.29
Kristina CollierOffice SuppliesCentral26/01/20153.08
James HunterTechnologyCentral7/01/201579.02
Wesley TateOffice SuppliesCentral15/01/2015447.89
Wesley TateTechnologyCentral21/01/20151420.84
Crystal FloydOffice SuppliesCentral15/01/2015111.97
Crystal FloydTechnologyCentral21/01/2015355.21
Rebecca LindseyOffice SuppliesCentral1/01/201536.58
Rodney FieldOffice SuppliesCentral30/01/2015123.03
Rodney FieldFurnitureCentral30/01/2015431.84
Rhonda SchroederOffice SuppliesCentral23/01/201537.89
Sherri P StephensOffice SuppliesCentral9/01/201510.19
Miriam GreenbergOffice SuppliesCentral3/01/2015131.34
Derek JerniganFurnitureCentral6/01/20156968.9
Stacey LucasOffice SuppliesCentral6/01/2015100.99
Stacey LucasTechnologyCentral6/01/2015142.06
Suzanne CochranOffice SuppliesCentral6/01/2015363.74
Suzanne CochranOffice SuppliesCentral6/01/201515.93
Suzanne CochranOffice SuppliesCentral6/01/201521.28
Sherri F VogelFurnitureCentral10/01/2015551.51
Sherri F VogelOffice SuppliesCentral10/01/2015142.79
Marianne CareyFurnitureCentral29/01/2015260.66
Mary HewittFurnitureCentral3/01/2015302.34
Philip HawkinsTechnologyCentral10/01/2015334.44
Melinda RogersFurnitureCentral5/01/2015248.84
Laurie HoweFurnitureCentral31/01/20151635.38
Laurie HoweOffice SuppliesCentral31/01/201558.48
Laurie HoweOffice SuppliesCentral31/01/201540.05
Eddie WalkerTechnologyCentral4/01/2015807
Bobby PowellTechnologyCentral5/01/201555.25
Bobby PowellTechnologyCentral5/01/2015551.22
Helen DickersonOffice SuppliesCentral26/01/2015577.75
James DavenportOffice SuppliesCentral29/01/201559.4
James DavenportOffice SuppliesCentral29/01/201566.46
Monica HarveyTechnologyCentral27/01/20151610.84
Nancy HoldenOffice SuppliesCentral24/01/201576.87
Glen RobertsonOffice SuppliesCentral6/01/2015558.41
Glen RobertsonOffice SuppliesCentral6/01/2015290.22
Arlene Wiggins DaltonOffice SuppliesCentral22/01/201542.56
Nicholas WallaceTechnologyCentral21/01/2015414.91
Yvonne CollierOffice SuppliesCentral22/01/2015140.7
Troy CaseyOffice SuppliesCentral3/01/201515.95
Karl KnowlesOffice SuppliesCentral9/01/201530.1
Danielle P RaoOffice SuppliesCentral17/01/2015448.47
Betsy PuckettFurnitureCentral5/01/20151000.26
Betsy PuckettFurnitureCentral5/01/20152184.99
Betsy PuckettTechnologyCentral5/01/20151191.2
Betsy PuckettTechnologyCentral7/01/2015165.71
Becky FarmerTechnologyCentral12/01/201531.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.

 

 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1714448267744.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors