Identify whether a record has many products

Question

I am using both Access and Power BI to solve this but to no avail. In principle it actually is quite simple but I need to do it in a particular way for subsequent pricing calculations.

Essentially, I have a table with transactions with service id. This service id will appear multiple times with one or multiple products. If it is just one product then I need to populate a column with "Single Product" otherwise "Multiple Product". See below for an illustration :

Serviceid  Product  Type
1          A        Multiple Products
1          B        Multiple Products
2          A        Single Product
3          A        Single Product
3          A        Single Product
3          A        Single Product

Answer 1

In Power BI, you can do this within Power Query:

let
    Source = SourceTable,
    fnDistinct = (MyTable, MyServiceid) =>
        let
            #"Filtered Rows" = Table.SelectRows(MyTable, each ([Serviceid] = MyServiceid)),
            #"Distinct Products" = List.Count(List.Distinct(#"Filtered Rows"[Product]))
        in
            #"Distinct Products",
    #"Added M Type" = Table.AddColumn(Source, "M Type", each if fnDistinct(Source, [Serviceid]) > 1 then "Multiple Products" else "Single Product", type text)
in
    #"Added M Type"

Or using DAX:

DAX Type = 
VAR CountProducts = 
    CALCULATE(
        DISTINCTCOUNT ( Table1[Product] ),
        ALLEXCEPT ( Table1, Table1[Serviceid] )
    )
RETURN
    SWITCH ( 
        CountProducts,
        1, "Single Product",
        "Multiple Products"
    )

Worked example PBIX file: https://pwrbi.com/so_55918190/

Performance testing DAX solution, on 250,000 rows: