How to calculate the right "Not assigned" orders (PO type) by filtering order PO types

[Anonymous]

I want to countdistint ordernummers that has unassigned as PO type excluding the one which has also other PO types.

I made this DAX formula but it does not work

Column 2 =

(IF('Automatic orders'[Po type]="Not assigned",DISTINCTCOUNT('Automatic orders'[order number])))-(IF('Automatic orders'[Po type]="Not assigned",

IF(

COUNTX(FILTER('Automatic orders',[order number]=EARLIER('Automatic orders'[order number])),[order number])=1,

1,0),0))

order number	Po type
348440249	EDI
348440249	Not assigned
348608424	EDI
348608424	Not assigned
348711206	Not assigned
348711206	OCRE
348731151	EDI
348731151	Not assigned
348731153	EDI
348731153	Not assigned
348731156	EDI
348731156	Not assigned
348731157	EDI
348731157	Not assigned
348791606	NE2O
348791606	NE2O
348791606	Not assigned
348873298	NE2O
348915922	Not assigned
348915922	WEBL
348990448	MOBI
348990448	Not assigned
349021881	Not assigned
349040425	NE2O
349040425	Not assigned
349070158	EDI
349070158	Not assigned
349096846	MOBI
349096846	Not assigned
349190698	Not assigned
349190698	WEBL
349203619	MAIL
349203619	Not assigned
349259099	Not assigned
349259099	WEBL
349284916	NE2O
349284916	Not assigned
349297455	EDI
349297455	Not assigned
349298369	MOBI
349298369	Not assigned
349407297	Not assigned
349407297	WEBL
349430925	EDI
349430925	Not assigned
349432914	NE2O
349432914	Not assigned
349443981	Not assigned
349462818	Not assigned
349467057	NE2O
349480643	Not assigned
349495918	NE2O
349495918	Not assigned
349504168	EDI
349504886	NE2O
349504886	Not assigned
349512891	EDI
349512891	Not assigned

[jameszhang0805]

Please try this code:

[jameszhang0805]

Corrected the code 

[Anonymous]

Hello jemaszhang0805, sorry for my late reaction. No failurues popping up but it only counts the rows which is 79  but the result should be 43.

[jameszhang0805]

@Anonymous 

My code is just based on the data source you provided. I created a pivot table in Excel it returned the same result. So maybe it's the other columns in your original data source that are affecting the filtered result.

[Anonymous]

It is right. I have made a column just for this table but it does not work and unfortunatily I can not attache the power bi file.  See picture.

[jameszhang0805 · Accepted solution]

@Anonymous  if you put the code into the column, it will generate the wrong result. Because my code is used to put into a measure. 
For calculate column code:

CorrectPOColumn = 
VAR _NormalPO =
    CALCULATETABLE (
        SUMMARIZE ( 'PO Detail', 'PO Detail'[order number] ),
        'PO Detail'[Po type] <> "Not assigned"
    )
VAR _Notassiged =
    EXCEPT ( VALUES ( 'PO Detail'[order number] ), _NormalPO )
RETURN
    IF( 'PO Detail'[order number]   in _Notassiged ,1 ,0)

[Anonymous]

Thnx jameszhang0805, it is working now.

[Anonymous]

Sorry wrong picture,

[AntrikshSharma]

@Anonymous Did the above code solve your problem?

[Anonymous]

Hello AntrikshSharma,  for both solutions it is only counting the rows in the table which is 79. The result should be 43.

[amitchandak]

@Anonymous , A measure with only Not assigned

countx(filter(summarize(Table, Table[order number], "_1", distinctCOUNT(Table[Po type]) , "_2", calculate(distinctCOUNT(Table[Po type]), filter(Yable, Table[Po type] ="Not assigned"))), [_1] =[_2]), [order number])

[Anonymous]

Hello amitchandak, sorry for my late reaction. The DAX formula does not give a syntax or other failures when creating a new colum but is counts only the rowes in my table namely 79