TOPN Staff in Bar Chart

[Vincem35]

Dear Community, 

I am working with a transaction table and would like to highlight in a bart chart the top 3 staff.

I want to use TOPN function and not Rankx as the last one is very slow.

I created a measure like below

It its very fast - but when i pull it in a bar chart together with my column "Staff" i am getting all the Staff and not only the top 3.

Var Min_Date = "2019-01-01"
var Max_Date = "2019-12-31"
Var Table_Temp= 
    TOPN(3,
    CALCULATETABLE(
    SUMMARIZE('POS Boutique Transactions', 'POS Staff'[STAFF NAME],
    "Total Clients", DISTINCTCOUNT('POS Boutique Transactions'[Client])),
    all('POS Staff'[STAFF NAME]),
    'POS Boutique Transactions'[CLIENT TYPOLOGY] = "NEW",
    'POS Boutique Transactions'[VALID STAFF]= true(),
    'POS Boutique Transactions'[REGISTERED CLIENT] = true(),
    filter('POS Boutique Transactions','POS Boutique Transactions'[DT_VALID_TO] > Max_Date),
    filter('POS Boutique Transactions', 'POS Boutique Transactions'[DT_DOCUMENT]>= Min_Date && 'POS Boutique Transactions'[DT_DOCUMENT] <=Max_Date)), [Total Clients])

Var calc = SUMX(Table_Temp,  [Total Clients])

Return calc

How can i get only the top 3 staff in this chart?

Thanks for your help

[v-lionel-msft]

Hi @Vincem35 ,

Have you tried using a filter instead of the TOPN() function?

Best regards,
Lionel Chen

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

[Vincem35]

Thanks for your answer
still this way gives me a very slow performance... any chance to have a calulated table?

[amitchandak]

@Vincem35 , Try like

Total clients =
Var Min_Date = "2019-01-01"
var Max_Date = "2019-12-31"
return
calculate(DISTINCTCOUNT('POS Boutique Transactions'[Client]),
all('POS Staff'[STAFF NAME]),
'POS Boutique Transactions'[CLIENT TYPOLOGY] = "NEW",
'POS Boutique Transactions'[VALID STAFF]= true(),
'POS Boutique Transactions'[REGISTERED CLIENT] = true(),
filter('POS Boutique Transactions','POS Boutique Transactions'[DT_VALID_TO] > Max_Date),
filter('POS Boutique Transactions', 'POS Boutique Transactions'[DT_DOCUMENT]>= Min_Date && 'POS Boutique Transactions'[DT_DOCUMENT] <=Max_Date))

top 3 =
CALCULATE([Total clients],TOPN(3,all('POS Staff'[STAFF NAME]),[Total clients],DESC),VALUES('POS Staff'[STAFF NAME]))