Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 | |
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 |
Solved! Go to 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)
Please try this code:
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.
@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.
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.
@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)
Thnx jameszhang0805, it is working now.
Sorry wrong picture,
@Anonymous 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.
@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])
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |