Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
pedroccamaraDBI
Post Partisan
Post Partisan

Count contracts per client on a slicer

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


Capture.JPG
Thank you all in advance

1 ACCEPTED SOLUTION

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]))

View solution in original post

6 REPLIES 6
philouduv
Resolver III
Resolver III

Hey @pedroccamaraDBI ,

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

philouduv_0-1651495603303.png

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

philouduv_1-1651495775442.png


Best regards,

 

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

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]))

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

amitchandak
Super User
Super User

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

 

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.