Help with solution for multiple criteria

Hi Anonymous ,

How about this?

1. Create measures.

Customers purchased more brand = 
COUNTROWS (
    FILTER (
        VALUES ( Sales[customer_key] ),
        COUNTROWS (
            INTERSECT (
                CALCULATETABLE ( VALUES ( Sales[Brand_key] ) ),
                VALUES ( Brand[Brand_key] )
            )
        )
            = COUNTROWS ( VALUES ( Brand[Brand_key] ) )
    )
)

Customers purchased more brand 2 = 
VAR MinBrandKey =
    MIN ( Brand[Brand_key] )
VAR t =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                Sales,
                "Last_", CALCULATE (
                    MAX ( Sales[latest_order_date] ),
                    FILTER (
                        ALLSELECTED ( Sales ),
                        Sales[market_key] = MAX ( Sales[market_key] )
                            && Sales[customer_key] = MAX ( Sales[customer_key] )
                    )
                )
            ),
            [Last_] = [latest_order_date]
                && [Brand_key] = MinBrandKey
        ),
        [customer_key]
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            COUNTROWS (
                INTERSECT (
                    CALCULATETABLE ( VALUES ( Sales[Brand_key] ) ),
                    VALUES ( Brand[Brand_key] )
                )
            )
                = COUNTROWS ( VALUES ( Brand[Brand_key] ) )
        )
    )

Customers purchased more brand and latest order date in min selected brand =
SUMX ( VALUES ( Sales[customer_key] ), [Customers purchased more brand 2] )

Title 1 =
IF (
    COUNTROWS ( VALUES ( Brand ) ) = 1,
    "Total no customers in " & SELECTEDVALUE ( Brand[Brand_name] ),
    "Customers purchased in "
        & CONCATENATEX ( VALUES ( Brand[Brand_name] ), [Brand_name], " & " ) & " (in same market)"
)

Title 2 = 
IF (
    COUNTROWS ( VALUES ( Brand ) ) = 1,
    "Total no customers in " & SELECTEDVALUE ( Brand[Brand_name] ),
    "Customer purchased in "
        & CONCATENATEX ( VALUES ( Brand[Brand_name] ), [Brand_name], " & " ) & " with latest purchase in "
        & MIN ( Brand[Brand_name] ) & " in same market"
)

Total no. customers = DISTINCTCOUNT(Sales[customer_key])

2. Create visuals.

3. Test.

BTW, .pbix file attached.

Best Regards,

Icey

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