topic Count contracts per client on a slicer in DAX Commands and Tips

Count contracts per client on a slicer

pedroccamaraDBI — Mon, 02 May 2022 12:33:19 GMT

Hello all
I would like to have in a slicer the number of contracts made.
Now, i want to know, for example, which clients has 3 contracts or whatever number i choose.
My fact table, contracts, is connected to clients, many to one.
Can you help me on step by step proccess, because last time i thought the solution was good but it turns out not to be good.
This is what i would like to see

Thank you all in advance

Re: Count contracts per client on a slicer

amitchandak — Mon, 02 May 2022 12:45:13 GMT

pedroccamaraDBI , seem like you need Avg no of contract per client

AverageX(summarize(Contract, Clinet[Client], "_1", Count(Contract[Contract no]) ), [_1])

Re: Count contracts per client on a slicer

philouduv — Mon, 02 May 2022 12:49:42 GMT

Hey pedroccamaraDBI ,

First create a calculated column on you dimension table containing all clients name (the one part of your relationship)

Then add this column to a slicer if you want to be able to select the number of contract :

Best regards,

Re: Count contracts per client on a slicer

pedroccamaraDBI — Mon, 02 May 2022 14:41:17 GMT

Hello philouduv
Could you please check your measure because i can't do it here. Doesn't allow me to write the filter expression

Re: Count contracts per client on a slicer

pedroccamaraDBI — Mon, 02 May 2022 14:43:42 GMT

Hello amitchandak
I really need the total for each client so i can filter it later on, and may know, for example, which clients has exactly 3 contracts

Re: Count contracts per client on a slicer

philouduv — Mon, 02 May 2022 14:50:49 GMT

Hey pedroccamaraDBI , it is not a measure but a calculated column that you create on your DIMENSION table (the one with each id/name occuring only 1 time),
And for your example it would give something like :

nbr_contract = CALCULATE(COUNTROWS(FactTable),

FILTER(ALL(FactTable),

DimTable[cliend id] == FactTable[cliend id]))

Re: Count contracts per client on a slicer

pedroccamaraDBI — Mon, 02 May 2022 18:14:03 GMT

Thanks philouduv
Now it's working....Thank you so much