topic Re: How to calculate the right "Not assigned" orders (PO type) by filtering order PO types in DAX Commands and Tips

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

Anonymous — Wed, 17 Feb 2021 17:03:37 GMT

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

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

amitchandak — Thu, 18 Feb 2021 03:41:01 GMT

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])

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

jameszhang0805 — Thu, 18 Feb 2021 06:33:08 GMT

Please try this code:

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

jameszhang0805 — Thu, 18 Feb 2021 11:39:05 GMT

Corrected the code

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

Anonymous — Mon, 22 Feb 2021 14:28:13 GMT

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

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

AntrikshSharma — Mon, 22 Feb 2021 14:54:07 GMT

Anonymous Did the above code solve your problem?

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

Anonymous — Tue, 23 Feb 2021 09:05:13 GMT

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.

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

Anonymous — Tue, 23 Feb 2021 09:07:00 GMT

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

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

jameszhang0805 — Tue, 23 Feb 2021 09:43:40 GMT

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.

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

Anonymous — Tue, 23 Feb 2021 13:33:14 GMT

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.

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

Anonymous — Tue, 23 Feb 2021 16:28:04 GMT

Sorry wrong picture,

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

jameszhang0805 — Wed, 24 Feb 2021 07:33:55 GMT

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)

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

Anonymous — Thu, 25 Feb 2021 07:45:36 GMT

Thnx jameszhang0805, it is working now.