Customer count not showing correctly

[mp390988]

This is my model.

I am trying to show a visual that shows the customer count per year so I created a measure called count of customers which is defined as belows. The reason why I am using crossfilter is because if you look at my model, there is no direct relationship between DimDate and DimCustomer. Also, the model shows that the filters from DimCustomer do not propagate into DimDate.

count of customers = CALCULATE(
    COUNTROWS(DimCustomer),
    CROSSFILTER(FactInternetSales[OrderDateKey],DimDate[DateKey],Both)
)

As you can see, I keep getting repeated values of 18484.

What am I doing wrong here?
You can find the pbix file here 

[Deku · Accepted solution]

You can use the expanded table context to get the answer

count of customers =

Countrows(

Summarize( FactInternetSales,  DimCustomer[customer key]

)

)

[lbendlin · Accepted solution]

... or you can let the data model do that work for you

[mp390988]

Just for my understanding, any particular reason why the cross filter method shows repeating total against calender year? Is there anything wrong with this method?

[Deku · Accepted solution]

The crossfilter should of be on sales <-> customer rather than date. Filters travel in the directions of the arrow, in your model the relationship is customer -> sales, so the filter cannot travel to customer as the arrow is pointing the wrong way.

The simpliest thing was to do a distinctcount( sales[customerId] ) then you don't have to bother travelling from the sales table. Would only be worth it if you wanted to count a attribute that was in the customer table

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Deku · Accepted solution]

You can use the expanded table context to get the answer

count of customers =

Countrows(

Summarize( FactInternetSales,  DimCustomer[customer key]

)

)

[lbendlin · Accepted solution]

... or you can let the data model do that work for you

[mp390988]

Apologies.

Does the link work now?

[lbendlin]

Link requires access, please check.

Bidirectional filters are another way of saying "cartesian product". They are almost never the answer.