Forum Discussion
Creating a measure based on a dimension
Hello,
I need a little help with understanding how to create measures based on a dimension table.
I have a simple model connecting Orders with Customers, Calendar and Country table.
I would like to calculate how many types of customers have placed orders for any country but in a specific month.
This is the model:
Here is what I need to get (column Customer Types).
There are more months and countries in this table - this is just a cut)
I was able to get the good result for
Customer Types =
CALCULATE(
DISTINCTCOUNT(Order[CustomerType]),
ALL(Country)
)
But at the end I will not have the column Order[CustomerType] available in the model so I can't use it.
My first aproach was to create a simple count
Customer Types ver 1 =
DISTINCTCOUNT(Customer[CustomerType])
The problem is that is showing me number of Customers Types for all periods and coutnries
Second aproach is to include the Order table in the calculation
Customer Types ver 2 =
CALCULATE(
DISTINCTCOUNT(Customer[CustomerType]),
Order
)
This way the number of Customer Types is now split by month but also by country.
I have tried another solution:
Customer Types ver 3 =
CALCULATE(
DISTINCTCOUNT(Customer[CustomerType]),
Order,
ALL(Country)
)
This returns the exact same numbers as version 2.
If you have an idea on how to do this I would appreciate help.
Thanks