Distinct Count problem

[shophoney]

[FONT=&quot]Hi, I have an issue with a DAX formula and have spent over a week trying to resolve it.[/FONT]
[FONT=&quot]I would like to know the number of sale transactions each day. But here’s the quandary:[/FONT]

• If the total number of units sold after returns is zero, ignore transaction.
• If total units sold is >0 then count it 1
• If total units sold is less <0 count it -1

[FONT=&quot]Here is my current formula:[/FONT]
[FONT=&quot]TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0)-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0)

[/FONT]
[FONT=&quot]Correct answer should be 43 units sold and 32 transactions[/FONT]
[FONT=&quot]The issue is with transaction 206, we had a return and a sale in the same transaction. So it met both criteria being both +1 and -1 = 0[/FONT]
[FONT=&quot] [/FONT]

	Column Labels
	9200
Row Labels	TRANS# DISTINCT COUNT	SALES TOTAL UNITS SOLD
08/14/18
172	1	1
173	-1	-1
174	1	1
175	0	0
176	1	1
177	0	0
178	1	1
179	1	2
180	1	3
181	1	1
182	1	3
183	1	1
184	1	1
185	1	1
205	1	1
206	0	3
207	1	1
208	1	1
209	1	1
210	0	0
211	1	1
212	1	1
213	1	1
214	1	1
215	1	1
216	1	1
217	-1	-3
218	1	1
219	1	2
220	1	3
221	1	2
222	1	1
223	1	1
224	1	2
225	1	1
226	1	2
227	1	1
228	1	1
229	1	1
08/14/18 Total	31	43

<colgroup><col><col><col></colgroup><tbody>
</tbody>

[FONT=&quot]Thanks[/FONT]

 

[gazpage]

I don't think I understand your question, otherwise this would just be something like:

Measure:=
VAR TotalSoldUnits = SUM ( 'Sales Table'[Units Sold])

RETURN
SWITCH (
    TRUE(),
    TotalUnitsSold > 0, 1,
    TotalUnitsSold <0, -1,
    0
)