Hi Vivian,

Create a dimension table that maps the clients to their respective groups e.g.

Client  Group

A        A

B        B

C        Other

D        Other

E        Other

Then create a relationship between your fact and dim tables. Then create your visuals using the Group field.

Mynda

thanks Mynda, is there any alternative way to do it? We have thousands of clients and 10 clients are on watch list and rest will be shown as others.

And the client data will be grown from time to time.

Regards 

Hi Vivian,

It should be fairly straight forward. Using Power Query, extract a list of distinct client names from your fact table. This table will become your dim table.

If you have the 10 'watch' clients listed in a separate table, you can use an 'if' statement to check if the name on each row of your dim table matches the name in the watch list table, if so, return their name, if not return 'other'. Then you will have a dimension table classified into those you want to watch and those that are 'other'.

Alternatively, you can add a conditional column to your new dim table and add each watch client as a separate criteria. e.g. if [client] = "Client A" then "Client A, else if [client] = "Client B" then "Client B" .... else "Other".

Hope that makes sense.

Mynda

Thnx Mynda.