Count values in another table

Hi SAPpowerbi ,

Here's my solutions.

Sample data:

1.Create a count measure to count the service line.

Count = CALCULATE(COUNT('Table'[Service Line]),ALLEXCEPT('Table','Table'[Clint name]))

2.Create another three measures to count three situations.

Revenue in 1 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=1))+0

Revenue in 2 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=2))+0

Revenue in 3 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=3))+0

3.Put three measures into a matrix visual, and then switch values to rows.

Another solution:

1.Create a table by entering data.

2.Create a measure with SWITCH function to count.

Count based on service line =
SWITCH (
    MAX ( 'Table (2)'[Service line 1/2/3] ),
    "Revenue in 1 Service line",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Clint name] ),
            FILTER ( 'Table', [Count] = 1 )
        ) + 0,
    "Revenue in 2 Service line",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Clint name] ),
            FILTER ( 'Table', [Count] = 2 )
        ) + 0,
    "Revenue in 3 Service line",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Clint name] ),
            FILTER ( 'Table', [Count] = 3 )
        ) + 0
)

3.Put the column and the meaesure into a table visual.

Best Regards,

Stephen Tao

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