Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Lets say I have this Data
I need to only count the Carrier Reference that has a status of "Carrier Invoice Posted" in my final numbers, so that i can show true #s (no duplicates) on my new formula column. The current count of carrier reference, is a simple count. can anyone guide me to create a DAX measure for this so that I DO NOT count a duplicate carrier reference that has a life cycle status of Rejected
Solved! Go to Solution.
thank you for the Reply Vijay,
I think I posted my question incorrectly if a carrier reference has a duplicate with a LifeCycleStatus = Reject & LifeCycleStatus = Carrier Invoice posted, then I only want to count the reference of the carrier invoice posted status.
Not sure if this can be obtained with a DAX formula or a step in the transform data.
@Anonymous
Also you can try
CALCULATE(
Count( Life cycle status),
Life Cycle status = "Carrier Invoice Posted in SAP ERP")
LEt me know if it solves
Proud to be a Super User!
thank you for the Reply Vijay,
I think I posted my question incorrectly if a carrier reference has a duplicate with a LifeCycleStatus = Reject & LifeCycleStatus = Carrier Invoice posted, then I only want to count the reference of the carrier invoice posted status.
Not sure if this can be obtained with a DAX formula or a step in the transform data.
@Anonymous , Try a measure like
calculate(distinctcount(Table[Carrier Reference]), filter(Table, search("Carrier Invoice Posted", Table[Life Cycle Status],,0)>0))
calculate(count(Table[Carrier Reference]), filter(Table, search("Carrier Invoice Posted", Table[Life Cycle Status],,0)>0))
@amitchandak this is defenitely going in the right direction.
The idea is to get a distinct count of all carrier reference, making sure that there are no carrier reference duplicates with 2 different life cycle Status.
The new Formula column is the formuala that you entered previously
I hope this image helps
Hi @Anonymous ,
You can update the formula of measure [New Formula] as below:
New Formula =
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'RawData'[Life Cycle Status] ),
FILTER (
ALLSELECTED ( 'RawData' ),
'RawData'[Carrier Reference] = SELECTEDVALUE ( 'RawData'[Carrier Reference] )
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'RawData'[Carrier Reference] ),
FILTER (
'RawData',
SEARCH ( "Carrier Invoice Posted in SAP ERP", RawData[Life Cycle Status],, 0 ) > 0
&& _count = 1
)
)
Best Regards
unfortunately I am not getting any values
Hi @Anonymous ,
The measure which I provided is to get the count of Carrier Reference which only have one Life Cycle Status with "Carrier Invoice Posted in SAP ERP". I'm not sure whether I misunderstanding your requirement... Could you please make example to explain your desired result? What do you mean about count with no carrier reference duplicate? For example: the data in below table, what's your expected result?
Invoice | Carrier Reference | Life Cycle Status |
43 | ALB596 | Carrier Invoice Posted in SAP ERP |
59 | ALB596 | Rejected |
44 | ALB602 | Carrier Invoice Posted in SAP ERP |
74 | ALB602 | Rejected |
76 | ALB603 | Carrier Invoice Posted in SAP ERP |
58 | ALB604 | Rejected |
56 | ALB604 | Carrier Invoice Posted in SAP ERP |
12 | ALB604 | In Process |
Best Regards
Hi, Thank you for replying. If I where to have this data set
My expected result for this data set would be
Ready for Posting - 0
In Process - 1
Carrier Invoice posted in SAP ERP - 4
Rejected - 1
Hi @Anonymous ,
You can create two measures as below:
Status count per Carref =
CALCULATE (
DISTINCTCOUNT ( 'RawData'[Life Cycle Status] ),
ALLEXCEPT ( 'RawData', 'RawData'[Carrier Reference] )
)
Measure =
VAR _tab =
SUMMARIZE (
'RawData',
'RawData'[Carrier Reference],
RawData[Life Cycle Status],
"Flag",
IF (
(
[Status count per Carref] > 1
&& SELECTEDVALUE ( 'RawData'[Life Cycle Status] ) = "Carrier Invoice Posted in SAP ERP"
)
|| [Status count per Carref] = 1,
1,
0
)
)
RETURN
SUMX ( _tab, [Flag] )
Best Regards
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |