Count Number of Bundles sold based on Invoice number.

[kpfootball1029]

Hello, I am trying to setup a measure in Power BI that counts the number of a specific bundle that is sold at my company. I have an invoice data table along with a product table and I want to count how many times we sold the combination of "A, B, and C" Product Components on each unique invoice. In the below table the correct answer would be 2 because we have 3 positive occurrences of the bundle and 1 negative occurrence indicating a returned bundle. There are also situations where there may be 2 bundles sold on one invoice, or multiple of one item but you can only classify 1 "bundle" because the other items only have 1 unit each. Please let me know if you need more information to assist with this since I am relatively new to DAX. I greatly appreciate any assistance.

Invoice	Product Component	Units
111	A	1
111	B	1
111	C	1
111	D	1
222	A	2
222	B	3
222	C	2
333	A	1
333	C	1
333	X	1
333	V	1
444	B	1
444	A	1
444	V	1
555	A	-1
555	B	-1
555	C	-1

[Anonymous]

Hi @Anonymous.

I'm sorry to tell you that but I never look at code that is highly incomprehensible and complex. It's a waste of time. Code where var names are like a, b, c, d... is not something I enjoy reading. Good naming makes things so much easier.

And please don't get offended by this. I just don't have enough time to go over the measures and analyze them in detail.

Ask yourself a simple question: Which version of code would you like to maintain over time: mine or yours above? It's not enough to write something that kind of works. It has to be comprehensible and easy to change without breaking the existing functionality. This is the standard of programming I require.

Best
D

[Anonymous]

@Anonymous  You like criticising people 😀😁

[Anonymous]

@Anonymous, I don't. I'm just stating the rules I stick to and the rules I'm paid more than well to adhere to. That's because anyone can write sloppy code that kind of works. There are not many that write good comprehensible and therefore easy to maintain code which is easy for OTHERS to analyse. I just gave you reasons why I don't want to look at your code. And I gave you reasons why the readers of my code are glad I write it the way I do. I just respect my READERS.

Think about it. One day somebody will likely come to this thread and they will want to UNDERSTAND the code. Which one would you rather want to analyze? I hope this is a question that does not need to be answered.

So, you can take a lesson from it. Or you can take no heed. It's your call.

Best
D

[Anonymous]

Hi Greg.

Please do not use EARLIER. Use variables instead. EARLIER only obscures things and is completely redundant in the language with the introduction of variables.

Second, SUMMARIZE is a VERY dangerous function. Please do not do any calculations inside it. Instead, please use the combination SUMMARIZE/ADDCOLUMNS. I thought you did know you should NEVER use SUMMARIZE with calculations...

Best
D

[kpfootball1029]

I appreciate the reply D, do you have an example of what you would do? I am pretty new to DAX so not sure how I would edit the above Measure based on your input. Thank you! - Ken

[Anonymous]

// Columns must always be qualified
// with the table name. Measures
// should never be qualified with
// table name. The exception is 
// when you refer to columns added
// to a table via a function.

Measure =
VAR __Table =
    FILTER (
        'Table',
        'Table'[Units] > 0
    )
VAR __Table1 =
	ADDCOLUMNS(
		VALUES( 'Table'[Invoice] ),
        "A",
			var __currentInv = 'Table'[Invoice]
			return
        	SUMX (
	            FILTER (
	            	__Table,
	            	and(
	                	'Table'[Invoice] = __currentInv,
	                 	'Table'[Product Component] = "A"
	                 )
	            ),
            	'Table'[Units]
        	),
        	
        "B",
			var __currentInv = 'Table'[Invoice]
			return
        	SUMX (
	            FILTER (
	            	__Table,
	            	and(
	                	'Table'[Invoice] = __currentInv,
	                 	'Table'[Product Component] = "B"
	                 )
	            ),
            	'Table'[Units]
        	),
        	
        "C",
			var __currentInv = 'Table'[Invoice]
			return
        	SUMX (
	            FILTER (
	            	__Table,
	            	and(
	                	'Table'[Invoice] = __currentInv,
	                 	'Table'[Product Component] = "C"
	                 )
	            ),
            	'Table'[Units]
        	)
	    )
RETURN
    COUNTROWS (
        FILTER (
            __Table1,
            true()
            && [A] > 0
            && [B] > 0
            && [C] > 0
        )
    )

[kpfootball1029]

Hi D, thank you for the response! Similar to Greg's solution when I place this measure in a table with the Invoice numbers I only see Invoices 111 and 222 with the measure flagging 1 bundle per invoice. Ideally the output of the measure would show the below output. Is it possible to adjust the DAX measure to get this output? Thank you! - Ken

Invoice	Bundles (Measure)
111	1
222	2
333	0
444	0
555	-1
Total	2

[Anonymous]

Well, of course, it's similar because it's the same. I just translated what Greg produced into a more digestible and safer version. But the output must be the same.

Has @Anonymous got a better and correct solution? If he has, then let me know. If he has not, then I'll look into this a bit more.

Best
D