Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RafalK
Advocate IV
Advocate IV

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:

 

a1.png

 

Here is what I need to get (column Customer Types).

There are more months and countries in this table - this is just a cut)

 

a2.png

 

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

 
Rafał Kun
1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks,

This one works with some modifications:

 

Customers Type ver 3 =
CALCULATE(
  COUNTROWS(
    SUMMARIZE(
      Order,
      Customer[CustomerType]
      )
    ),
  ALL(Country)
)
Rafał Kun
az38
Community Champion
Community Champion

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

Безымянный.png


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.