DAX for creating a control group for statistical analysis

[RWhipple]

Hi, new (and inefficient) user here.  I'm trying to create a control group of similar customers in order to compare water consumption.  I have the database of All Customers and then one of Program Customers.  APN is the link.  I want to take each Program Participant  and find 2 Non Participant Customers in the All Customer file with an Average Monthly Allocation within +/- 2 units. Since the Program Customers are in the All Customer file they should be excluded from the output.

Any help would be appreciated!  Thanks, Rob

Example data is below

All Customers			Program Participants
APN	Average Monthly Allocation	APN	Average Monthly Allocation
2063-048-006	19.526			2069-076-008	22.064
2069-101-002	16.932			2069-038-080	15.647
2063-022-025	23.371			2069-063-022	19.929
2058-007-015	15.139
2049-007-027	20.290		Find 2 similar Non-Participant customers
4462-011-024	20.908		for each Participant with Allocation +/- 2 units
2049-017-045	21.860		(Exclude participants)
4456-011-064	16.715					DESIRED OUTPUT
4455-040-034	19.943		Program Participants	Control group of similar Non-participants
2063-016-082	18.751		APN	Allocation		APN	Allocation
4456-003-002	19.965		2069-076-008	22.064		2063-022-025	23.37094
2069-076-008	22.064					2069-076-008	22.06397
2069-038-080	15.647
2069-063-022	19.929		2069-038-080	15.6465		2058-007-015	15.13894
						4456-011-064	16.7147
			2069-063-022	19.92912		2063-048-006	19.52597
						4455-040-034	19.94319

[RWhipple]

Got it. Just do a new source. Thanks

[RWhipple]

Thanks!  I will try that.  So I would create a blank query each time I get a new batch of program data? Ex. I do it for Program 1 and then Program 2...etc.

[lbendlin]

Once you examined the code, replace the Source step with your own source.

[RWhipple]

@lbendlin  Thanks again for the code.  I was trying to drop in my own source but keep getting an error for Token Identifier Expected on the second Let statement. I just don't know the code well enough to know what I did wrong.  Here's the screenshot

Did I not insert the new source in the right spot?  Thanks again, Rob

[Anonymous]

Hi @RWhipple ,

It sounds like you're encountering a syntax error in Power Query. This error often occurs due to misplaced or missing elements in your code. You can refer the following blog to make troubleshooting and try to fix it.

Understanding Expression Syntax Errors in Power Query M

And you can copy and paste all of the codes which Ibendlin provided in your Advanced Editor to replace your original ones. You can open the Advanced Editor window by Clicking Home ribbon--> Advanced Editor in Power Query Editor. Note that you need to change the information in the Source section inside to your own data source.

Best Regards

[lbendlin]

You didn't catch the entire "Source" step.  That second "let"  line is from the meta data of the sample table.  Try again.

Note: You will want to enable "Display Line Numbers".  to make this easier.

[lbendlin]

Does it have to be DAX?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZDJDcUgDAV74Rws7+BaovTfxjeQKOEfkBDDeHnnWRhdKmqviF6OQgHG41KuY8KohJSQB3QI4Q9MkzmP5RsLSKMX2ijZKtKAZEASL9RYkNswETjwgaqeJSl7si4Y2HeT0tTZk6D7xzRfpuuats3mD7S0sKLo2jNU9lUo7T737NCM9rIoTwhpum0JYRsfxpDMcDf/UMkkOq4UXNsfXSHehTkWvX4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [APN = _t, #"Average Monthly Allocation" = _t, Participant = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Participant", Int64.Type}, {"Average Monthly Allocation", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Control Group", (k)=> if k[Participant]=1 then Text.Combine(Table.FirstN(Table.Sort(Table.AddColumn(Table.SelectRows(#"Changed Type",each [Participant] = null),"Distance",each Number.Abs(k[Average Monthly Allocation]-[Average Monthly Allocation])),{"Distance",Order.Ascending}),2)[APN],", ") else null, type text)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.