cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Humberto1959
Frequent Visitor

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

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 numberPo type
348440249EDI
348440249Not assigned
348608424EDI
348608424Not assigned
348711206Not assigned
348711206OCRE
348731151EDI
348731151Not assigned
348731153EDI
348731153Not assigned
348731156EDI
348731156Not assigned
348731157EDI
348731157Not assigned
348791606NE2O
348791606NE2O
348791606Not assigned
348873298NE2O
348915922Not assigned
348915922WEBL
348990448MOBI
348990448Not assigned
349021881Not assigned
349040425NE2O
349040425Not assigned
349070158EDI
349070158Not assigned
349096846MOBI
349096846Not assigned
349190698Not assigned
349190698WEBL
349203619MAIL
349203619Not assigned
349259099Not assigned
349259099WEBL
349284916NE2O
349284916Not assigned
349297455EDI
349297455Not assigned
349298369MOBI
349298369Not assigned
349407297Not assigned
349407297WEBL
349430925EDI
349430925Not assigned
349432914NE2O
349432914Not assigned
349443981Not assigned
349462818Not assigned
349467057NE2O
349480643Not assigned
349495918NE2O
349495918Not assigned
349504168EDI
349504886NE2O
349504886Not assigned
349512891EDI
349512891Not assigned
1 ACCEPTED SOLUTION

@Humberto1959  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)

jameszhang0805_0-1614151986782.png

 

View solution in original post

12 REPLIES 12
jameszhang0805
Resolver IV
Resolver IV

Please try this code:

WeChat Image_1.png

jameszhang0805_0-1613648332649.png

Corrected the code 

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.

@Humberto1959 

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.

WeChat Image_20210223172737.png

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.

Humberto1959_0-1614087144609.png

 

@Humberto1959  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)

jameszhang0805_0-1614151986782.png

 

View solution in original post

Thnx jameszhang0805, it is working now.

Sorry wrong picture,

 

Humberto1959_0-1614097670503.png

 

@Humberto1959 Did the above code solve your problem?

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

amitchandak
Super User IV
Super User IV

@Humberto1959 , 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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors