Forum Discussion
Measures as a dimension?
So I don't know why I can't get the grasp on how to do this.
For a simple version i have an "Orders" table with just 3 columns
OrderID | CustomerID | Date
I want a matrix visual that shows me
How many customers purchased a total of 1 order, 2 orders, 3 orders etc etc.
I simple can't find a way to do this using measures to distinctcount orders and customers respectively.
I need it to be measures, since the user input has to be a date range or similar (or other filters in the model which is vastly greater than the simplified table above).
Output would simple just be something like:
#Orders | #Customers
1 | 200
2 | 75
3 | 47
4 | 35
5 | 21
I can easily do this as a calculated column on a seperate customer table, but that wont allow dynamic filtering/slicing.
2 Replies
AlexaderMilland Create a separate, disconnected table to store your order numbers in a single column
1
2
3
4
5
Put that in a table visual along with this measure:
Measure = VAR __Count = MAX('Disconnected Table'[Orders]) VAR __Table = SUMMARIZE( 'Orders', [CustomerID], "__Value", COUNTROWS('Orders') ) VAR __Result = COUNTROWS( FILTER( __Table, [__Value] = __Count ) ) RETURN __ResultYou mean an index table that just goes from 1 to infinity (or well some max i set) ?
