Total Count of records based on Measure Value

[Anonymous]

Hello Experts,

I am looking for help for calculating the Total Count of Distinct Customer Names in Customer Dimension based on a Measure (M Value) in DimCustomer and at the same time the count should be dynamic to change with filter selection. Something like

My Data Model is like

Here

M Value = SUMX(RELATEDTABLE(FactSales),FactSales[Revenue] )

Customer Count = COUNTROWS(ALL(DimCustomer[CustomerName]))

I know I did COUNTROWS on ALL and no filter will be applied. So what I am looking for is

CustomerCount where

1. M Value is not BLANK()
2. Distinct Customer Name
3. Can be filtered based on Country or any other column

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try

Customer Count = Distinctcount(DimCustomer[CustomerName])

or

Customer Count = calculate(Distinctcount(DimCustomer[CustomerName]),all(DimCustomer))

[Anonymous]

Hi @amitchandak 

Distinct Count wont work as it will give me the count = 1 at each customer level where as i want total count

and Customer Count = calculate(Distinctcount(DimCustomer[CustomerName]),all(DimCustomer)) is same as 

Customer Count = COUNTROWS(ALL(DimCustomer[CustomerName]))

which gives me total distinct count at each row but my requirement 1 and 3 is still not covered. 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please refer to the following measure:

Customer Count = CALCULATE(Distinctcount(DimCustomer[CustomerName]),FILTER(ALLSELECTED(DimCustomer),[M Value]<> Blank())

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thanks Dedmon, 

The solution is acceptable. I was close enough but could not strike ALLSELECTED

Customer Count = CALCULATE(Distinctcount(DimCustomer[CustomerName]),FILTER(DimCustomer,[M Value]<> Blank())