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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DeeA
Frequent Visitor

DIstinct count based on multiple conditons

Hi @parry2k,

 

Can you please assist;

I have a table that looks like below;

DeeA_0-1695076364265.png

I want to be able to

1. countdistinct the number of cities per customer for each month for an output similar to below

DeeA_1-1695076609116.png

 

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

 

DeeA_3-1695077257786.png

 

Thanks

 

 

 

3 REPLIES 3
Anonymous
Not applicable

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:

vyangliumsft_0-1695189429467.png

Question2:

1. Enter data – create a flag table.

vyangliumsft_3-1695189459956.png

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:

vyangliumsft_4-1695189459962.png

 

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;

 

Distinct regions = CALCULATE(
    DISTINCTCOUNT('Table'[city]),
    ALLEXCEPT('Table', 'Table'[customer], 'Table'[Mth])
)
which worked well..gave me the distinct number of cities the customers visited over the 8month period

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

lbendlin
Super User
Super User

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...

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.