Distinct count of values from group A that do not exist in group B

[Anonymous]

Hi Everyone!

I'm struggling a long time with the following issue: 

I have 2 groups of items that I calculated - > A and B.

I need to count all distinct values from Group B that do not exist in group A -> Group C.

The following query will demonstrate it:

New group - C =

VAR A =

CALCULATE(VALUES(order[Material_Number]),

FILTER(order,order[Upsale_Amount]=0)

)

VAR  B =

CALCULATE(VALUES(order[Material_Number]),

FILTER(order,order[Upsale_Amount]>=1))

VAR C = IF(VALUE(UpSale_Items)=VALUE(Origin_Items),0,1)

return C

The last part of the query (var c) causing the issue.

When I add the measure to a table it says that the query returns multiple values instead of one. 

Anyone has any idea how to solve it?

[Fowmy]

@Anonymous 

Not sure if you need a new table or measure, 

For distinct count measure use the following:

New group Measure =
VAR A = CALCULATETABLE (VALUES(order[Material_Number]),  order[Upsale_Amount] = 0)
VAR B = CALCULATETABLE (VALUES(order[Material_Number]), order[Upsale_Amount] >=1)
VAR C =  DISTINCTCOUNT ( EXCEPT ( A , B ) )

For Table:

New group Table =
VAR A = CALCULATETABLE (VALUES(order[Material_Number]),  order[Upsale_Amount] = 0)
VAR B = CALCULATETABLE (VALUES(order[Material_Number]), order[Upsale_Amount] >=1)
VAR C =  EXCEPT ( A , B )

[Anonymous]

Hi Fowmy!

Thanks for your response. 

I tried the measure you created but the last line of the query returns an error. 

[Fowmy]

@Anonymous 
If you need a measure, you need to use the first code block that I shared, for the table, you the 2nd one

[Anonymous]

Yeh I know, what I'm tring to say that I get an error in the syntax of the measure. 

It doesn't let me to use distinctcount - as showcased in the image I added in my previous answer. 

[Fowmy · Accepted solution]

@Anonymous 

Please try now, I didn't test it, 

New group Measure =
VAR A = CALCULATETABLE (VALUES(order[Material_Number]),  order[Upsale_Amount] = 0)
VAR B = CALCULATETABLE (VALUES(order[Material_Number]), order[Upsale_Amount] >=1)
VAR C =  COUNTROWS( DISTINCT ( EXCEPT ( A , B ) ) )
RETURN
C

Switch A and B within EXCEPT if you don't get the results. 

[Anonymous]

Thanks a lot it works!!