Forum Discussion

RafalK's avatar
RafalK
Advocate IV
6 years ago

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

1 Reply

  • Hi RafalK ,

     

    A simple question. What kind of relationships have you created among these tables - single or bi-dierectional?

    Paste your data model from Power BI here.

     

    Thanks,

    Pragati