cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RustyNails Regular Visitor
Regular Visitor

Count number of items in category and verify if all of them have a specific status

Hello, I would like to verify the number of Orders in a table for each Customer, and if all the Orders were shipped. If All the orders were shipped, then I want to say "All Orders Complete" is "Yes" for that Customer.

 

Example: Lets call this Orders Table -

Customer_IDOrder IDOrder ShippedAll Orders Complete
A00011143095 No
A00011080930YesNo
A00011139737YesNo
B00022584618 No
B00022618961YesNo
C00033282832YesYes
C00033665548YesYes
C00033758698YesYes

 

Only C0003 is "Yes" for All Orders Complete because all three Orders for him were shipped. Then I want to present this data in this format in a visual (which I'll do)

Customer_IDAll Orders Complete
A0001No
B0002No
C0003Yes

 

The problem is, the Orders Table has specific SKU level information. I want to ignore the SKU level granularity and present the data at the Order level (and eventually at Customer level). So the granularity is Customer -> Order -> SKU.

 

This is what I have so far

All Orders Complete =
var temp = SUMMARIZE(OrdersTable, OrdersTable[Customer_ID], OrdersTable[Order ID], "Order Count", DISTINCTCOUNT(OrdersTable[OrderID]))"
return IF ( COUNTX(temp,[Order Count]) = DISTINCTCOUNT(OrdersTable[Order ID]), "Yes","No")

 

Any help is appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count number of items in category and verify if all of them have a specific status

Hi @RustyNails ,

You can try to use following measure formula if it suitable for your requirement:

Measure =
IF (
    CALCULATE (
        DISTINCTCOUNT ( T1[Order ID] )
            - CALCULATE ( DISTINCTCOUNT ( T1[Order ID] ), T1[Order Shipped] = "Yes" ),
        VALUES ( T1[Order ID] )
    ) = 0,
    "Yes",
    "No"
)

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Count number of items in category and verify if all of them have a specific status

Hi @RustyNails ,

You can try to use following measure formula if it suitable for your requirement:

Measure =
IF (
    CALCULATE (
        DISTINCTCOUNT ( T1[Order ID] )
            - CALCULATE ( DISTINCTCOUNT ( T1[Order ID] ), T1[Order Shipped] = "Yes" ),
        VALUES ( T1[Order ID] )
    ) = 0,
    "Yes",
    "No"
)

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 395 members 3,895 guests
Please welcome our newest community members: