Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
Thanks,
This one works with some modifications:
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |