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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Distinct count with filter showing wrong numbers

Hi,

Can any one help here what is the correct DAX formula ,

I have a measure to find out unique numbers of customers who is contributing 80% of the provision value (Measure [Prov USD])

Image 1 showing total provision by customer (all customers ,filtered as per slicer) also another table where i have shown with manual filter applied to visual to understand how many customers contributing 80% (If we take cumulative total of 8 customers provision [Prov USD] it is comming upto 80% of the total provision amount)

My measure Customers Contribute 80% showing correct values 8 in the card in image 1,

Where as in image 2 i have done an additional filter (Slicer selected year 2021 data table) compared to image 1,

the distinct customer count is showing 8 instead of 7 ( if you check the 2nd table it is showing only 7 customers)

Image 2

Measure

Customers Contribute 80% =
CALCULATE(
DISTINCTCOUNT(Data[Cr. Acc]),
FILTER('Customer DB',[Cumulative %]<=0.8))

Image 1

Image 2

Regards,

Ansar.

1 ACCEPTED SOLUTION
Helper II

Thanks @Fowmy @VahidDM @v-yingjl

It got resolved through FILTER functions.

Customers Contribute 80% =
CALCULATE(
DISTINCTCOUNT(Data[Cr. Acc]),
FILTER(ADDCOLUMNS(FILTER('Customer DB',[Cumulative Prov %]),"**bleep** %",[Cumulative Prov %]),[Cumulative Prov %]<=.8))

Regards,

Ansar.

8 REPLIES 8
Helper II

Thanks @Fowmy @VahidDM @v-yingjl

It got resolved through FILTER functions.

Customers Contribute 80% =
CALCULATE(
DISTINCTCOUNT(Data[Cr. Acc]),
FILTER(ADDCOLUMNS(FILTER('Customer DB',[Cumulative Prov %]),"**bleep** %",[Cumulative Prov %]),[Cumulative Prov %]<=.8))

Regards,

Ansar.

Super User

Hi @ansar

Can you share a [Cumulative %] measure code here?

Is that year slicer connected to "Customer DB" or "Data" tables? Can you share samples of your tables here?

Appreciate your Kudos✌️!!

Helper II

@VahidDM : Thanks for your reply

My fact table is connected to date table also with customer DB table,
Customer DB dont have direct relation ship with date table.

Cumilative Prov =
VAR CrNameProv = [Prov USD]
RETURN
IF(ISBLANK([Prov USD]),BLANK(),
SUMX(
FILTER(
SUMMARIZE(ALLSELECTED(Data),'Customer DB'[Cr. Name],
"Prov Amt", [Prov USD]),
[Prov Amt] >= CrNameProv),
[Prov Amt]))

Prov USD RemoveCust Filter =
IF(ISBLANK([Prov USD]),BLANK(),CALCULATE([Prov USD],ALLSELECTED('Customer DB')))

1. Cumulative % = DIVIDE([Cumilative Prov],[Prov USD RemoveCust Filter])

Community Support

Hi @ansar ,

What is the [Prov USD] measure look like? Notice multiple measures quoted this measure

In addition, you can consdier sharing a dummy sample file which just includes the Customer table, Data table and the Date table with the necessary columns in this issue.

Best Regards,
Community Support Team _ Yingjie Li

Super User

@ansar

Can you try the following measure? You can add the Customer Key or Code .

``````Customers Contribute 80% =
COUNTROWS (
FILTER ( VALUES ( 'Customer DB'[Customer ID] ), [Cumulative %] <= 0.8 )
)
``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II
@Fowmy i added factable customer column it is showing now 11 count instead of 7
Count = COUNTROWS ( FILTER ( VALUES ( Data[Cr. Acc]),[Cumulative %]<=.80))

Helper II
@Fowmy  Thanks for your swift reply !!
I have created below measure and it is showing 33K instead of 7
Count = COUNTROWS ( FILTER ( VALUES ( 'Customer DB'[Cr. Name]), [Cumulative %] <= 0.8 ) )

Regards,
Ansar.

Super User

@ansar

If you can share a dummy file with your scenario and show the issue then I can check on that.

Did I answer your question? Mark my post as a solution! and hit thumbs up

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors