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 t...
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 

     

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)
    )