Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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 

Anonymous
Not applicable

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.

@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.

WeChat Image_20210223172737.png

Anonymous
Not applicable

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

 

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

jameszhang0805_0-1614151986782.png

 

Anonymous
Not applicable

Thnx jameszhang0805, it is working now.

Anonymous
Not applicable

Sorry wrong picture,

 

Humberto1959_0-1614097670503.png

 

@Anonymous Did the above code solve your problem?

Anonymous
Not applicable

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
Super User

@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
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors