topic Re: Based on the Status change, need ORD ID Count. in DAX Commands and Tips

Based on the Status change, need ORD ID Count.

Anonymous — Tue, 19 Oct 2021 09:58:27 GMT

Hi All,

Below requirment might be easy but i am not able to get an idea how to achive. Thanks in Advance.

Requirement is:-

Scenario1: 1350 and 1361 is have Action Requested and Processed then need the Distinct Count of Ord ID.

Single Status, measn 1341 & 1351 is have only Processed need the count of ORD ID.

1342 &1370 is have only Action Requested this count of ORD ID is not needed.

ORD ID	Status
100001341	Processed
100001342	Action Requested
100001350	Action Requested
100001350	Processed
100001351	Processed
100001361	Action Requested
100001361	Processed
100001370	Action Requested

I need the count of Ord Ids based on the Status change. i need calculation like Count of Actioned Requested/Count of Processed

1350+1361/1350+1361+1341+1351

Two Actioned Requested and four Processesd.

Re: Based on the Status change, need ORD ID Count.

Jos_Woolley — Tue, 19 Oct 2021 11:39:19 GMT

Hi,

Actioned/Requested = VAR MyTable = SUMMARIZE ( 'Table', 'Table'[ORD ID], "Actioned", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status] = "Action Requested" ), "Processed", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status] = "Processed" ) ) RETURN DIVIDE ( SUMX ( MyTable, IF ( AND ( [Actioned] > 0, [Processed] > 0 ), 1 ) ), SUMX ( MyTable, IF ( [Processed] > 0, 1 ) ) )

Regards

Re: Based on the Status change, need ORD ID Count.

v-yingjl — Fri, 22 Oct 2021 07:18:44 GMT

Hi Anonymous ,

You can create a measure like this:

Change = VAR tab = ADDCOLUMNS ( 'Table', "Count", CALCULATE ( COUNT ( 'Table'[ORD ID] ), ALLEXCEPT ( 'Table', 'Table'[ORD ID] ) ) ) VAR a = COUNTX ( FILTER ( tab, [Count] >= 2 && [Status] = "Action Requested" ), [ORD ID] ) VAR b = CALCULATE ( COUNT ( 'Table'[ORD ID] ), 'Table'[Status] = "Processed" ) RETURN DIVIDE ( a, b )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.