Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |