Dax rollup rows

[Anonymous]

I have two levels. Product line level and above that, work order level. There can be multiple products per work order. I calculate an attribute about the product, but then need to roll that up to the work order level based on a hierarchy.

For example, work order a has products 1,2,3. Product 1 = Install. Product 2=Delivery. Product 3=Connect. I need Install to show up for work order a. The order is Install then Connect then Delivery. If there is any Install in any of the products for a work order, it should show up as Install. If there is no Install but there is a Connect, then it should show up Connect. If neither of those, then Delivery.

Ex.

Current		Output
Work Order	Product	DIC		Work Order	DIC
A	1	Delivery		A	Install
A	2	Install		B	Connect
A	3	Delivery		C	Delivery
A	4	Connect
B	1	Delivery
B	2	Delivery
B	3	Connect
C	1	Delivery
C	2	Delivery
C	3	Delivery
C	4	Delivery
C	5	Delivery

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

You can create a calculated table using below formula:

Summarize Product table =
SUMMARIZE (
    Product_table,
    Product_table[Work Order],
    "DIC", IF (
        CALCULATE (
            COUNT ( Product_table[DIC] ),
            FILTER (
                ALLEXCEPT ( Product_table, Product_table[Work Order] ),
                Product_table[DIC] = "Install"
            )
        )
            >= 1,
        "Install",
        IF (
            CALCULATE (
                COUNT ( Product_table[DIC] ),
                FILTER (
                    ALLEXCEPT ( Product_table, Product_table[Work Order] ),
                    Product_table[DIC] = "Connect"
                )
            )
                >= 1,
            "Connect",
            "Delivery"
        )
    )
)

Best regards,

Yuliana Gu

[Anonymous]

Output header should be farther to the right, above 2nd Work Order...