Forum Discussion

RafalK's avatar
RafalK
Advocate IV
6 years ago
Solved

Creating a measure based on a dimension table

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

 
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    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 

     

3 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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's avatar
      RafalK
      Advocate IV
      6 years ago

      Thanks,

      This one works with some modifications:

       

      Customers Type ver 3 =
      CALCULATE(
        COUNTROWS(
          SUMMARIZE(
            Order,
            Customer[CustomerType]
            )
          ),
        ALL(Country)
      )