Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi @parry2k,
Can you please assist;
I have a table that looks like below;
I want to be able to
1. countdistinct the number of cities per customer for each month for an output similar to below
2. For every month how do i show in a visual the count of movement like below;
A distinct cities visited of 1 means static
Thanks
Hi @DeeA ,
Here are the steps you can follow:
Question1:
1. Create measure.
Result1 =
COUNTX(
FILTER(ALL('Table'),
'Table'[Month]=MAX('Table'[Month])&&'Table'[Customer]=MAX('Table'[Customer])),[Cities visited])
2. Result:
Question2:
1. Enter data – create a flag table.
2. Create measure.
Result2 =
SWITCH(
TRUE(),
MAX('Move_Table'[Group])="Static",
CALCULATE(DISTINCTCOUNT('Table'[Customer]),
FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&[Result1]=0)),
MAX('Move_Table'[Group])="Move 2 times",
CALCULATE(DISTINCTCOUNT('Table'[Customer]),
FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&[Result1]=2)),
MAX('Move_Table'[Group])="Move 3 times",
CALCULATE(DISTINCTCOUNT('Table'[Customer]),
FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&[Result1]=3)),
MAX('Move_Table'[Group])="Move 4 times",
CALCULATE(DISTINCTCOUNT('Table'[Customer]),
FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&[Result1]=4)),
MAX('Move_Table'[Group])="Move 5 times",
CALCULATE(DISTINCTCOUNT('Table'[Customer]),
FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&[Result1]=5)))
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks a lot.
For Result1 i used the below;
2. a little modification to your second solution (result2) worked perfectly for me. However my table has 8 months. how do i modify the filter condition so that it considers all the months and not give me the results for just the maximum month. so that i can categorise (move 2 times, move 3 times, ..., move n times) for the distinct cities for all the customers over the 8 month period.
Thanks
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please note that Oklahoma is a state, not a city.
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |