Forum Discussion
Post PartisanCustomer count not showing correctly
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
You can use the expanded table context to get the answer
count of customers =
Countrows(
Summarize( FactInternetSales, DimCustomer[customer key]
)
)
... or you can let the data model do that work for you
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
7 Replies
- Deku
Super User
You can use the expanded table context to get the answer
count of customers =
Countrows(
Summarize( FactInternetSales, DimCustomer[customer key]
)
)
- lbendlin
... or you can let the data model do that work for you
- lbendlin
Link requires access, please check.
Bidirectional filters are another way of saying "cartesian product". They are almost never the answer.
mp390988Apologies.
Does the link work now?
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
- 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
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
