Creating a measure based on a dimension table

[RafalK]

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

[TomMartens · Accepted solution]

Hey @RafalK ,

next to the approach, @az38 suggested you can create a measure like this:

measure=
COUNTROWS(
SUMMARIZE(
'Order'
,'Customer'[CustomerType]
)
)

As the country and the date table are filtering the order table, the virtual table created by SUMMARIZE contains the "distinct" CustomerTypes by Country/Month, the number of customer types can be counted using COUNTROWS.

Personally I tend to avoid using the cross filter direction "Both" as long as possible.

Hopefully, this provides another idea of how to tackle your challenge.

Regards,

Tom 

[RafalK]

Thanks,

This one works with some modifications:

Customers Type ver 3 =

CALCULATE(

  COUNTROWS(

    SUMMARIZE(

      Order,

      Customer[CustomerType]

      )

    ),

  ALL(Country)

)

[az38]

Hi @RafalK 

from the very first sight it looks like if you will change Order-Customer relationships direction to Both you will be able add field CustomerType into visual aggregated as Count(Distinct) without any coding