To determine the approval flow of an order placed

[Thiru]

Hi,

I'm new to Power BI and facing issue in dtermining the Apprvoal Flow of an order. Please find the below data.

Order ID	Approvers
123	Manager
123	Category Manager
123	Interal Team
121	Manager
121	Internal Team
124	Internal Team

From the above table I need to find the no. of orders which got approved by 

1. Only Managers

2. Only Category Managers

3. Only Internal Team

4. Manger+ Category Managers

5. Manager+ Internal Team

6. Category Managers+Internal Team

7. Manger+ Category Managers+Internal Team.

Hence, from the above table the result should come as 

Approval Flow	Count
Manager+Category Manager+Internal Team	1
Manager+Internal Team	1
Internal Team	1

Any post regarding this would be appreciated.

Thank you

[Thiru]

I was able to restructe the data by adding 4 columns as shown below.

Order ID	Approvers	Count	Manager	Category Manager	Internal Team
123	Manager	3	Manager
123	Category Manager	3		Category Manager
123	Interal Team	3			Interal Team
121	Manager	2	Manager
121	Internal Team	2			Interal Team
124	Internal Team	1			Interal Team

Count : 

COUNTROWS(FILTER('Approval Flow',EARLIER('Approval Flow'[Order ID])='Approval Flow'[Order ID]))

Not sure, if this would help. Please provide your suggestions.
Thank you

[v-rzhou-msft · Accepted solution]

Hi @Thiru ，

I suggest you to create a calculated column in your table.

Approval Flow = CONCATENATEX(FILTER('Table','Table'[Order ID] = EARLIER('Table'[Order ID])),'Table'[Approvers]," + ")

Measure:

Count = CALCULATE(DISTINCTCOUNT('Table'[Order ID]))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thiru]

Hi @v-rzhou-msft ,

Thanks for the solution it works perfectly.

However, I'm facing an issue now. For eg: the above DAX considers Manger+Inertnal Team and Internal Team+Manager as differnet entitites but they are same. The count should appear under only one category.

Could you please help me on this?

Thank you