Forum Discussion
DAx to Create a dynamic Dimension Classification based on another dimension
Right heres my issue: I have 1 fact table on purchases, 2 dimensions (Products, Customers)
I want to create a customer type depending on the majority purchased product for a given date range.
Example:
| Division Dimension |
| Fruit |
| Meat |
| Veg |
| Date | Customer | Product Division | Tons |
| 01/01/2018 | John | Fruit | 7 |
| 02/01/2018 | John | Veg | 8 |
| 03/01/2018 | Meg | Fruit | 13 |
| 04/01/2018 | Meg | Meat | 16 |
| 05/01/2018 | Meg | Veg | 5 |
| 03/01/2018 | Sally | Fruit | 3 |
| 04/01/2018 | Sally | Meat | 2 |
| 05/01/2018 | Sally | Veg | 5 |
taking the maxium tonnage for that customer I want to asign a customer type to the customer Dimension (Example:
| Custom Dimension | Customer Type |
| John | Veg |
| Meg | Meat |
| Sally | Fruit |
Any thoughts?
Thanks
Hi Anonymous
From your example, it seems the “given date range” here is before 2018/5/1, so i use "date" column in a slicer and select "before 2018/5/1".
First i create a calendar date table
Table2 = CALENDARAUTO()
please note not to create relationship between this date table with your table.
Then create measures in your table ( called Table1 below)
max = MAX(Table2[Date]) min = MIN(Table2[Date]) max purchase = CALCULATE(MAX(Table1[Tons ]),FILTER(ALLEXCEPT(Table1,Table1[Customer]),[Date ]>=[min]&&[Date ]<[max])) max-produce = CALCULATE(MAX(Table1[Product Division ]),FILTER(ALLEXCEPT(Table1,Table1[Customer]),[Tons ]=[max purchase]))
Best Regards
Maggie
4 Replies
Measure = VAR __customer = MAX([Customer]) VAR __table = SUMMARIZE(ALL('table19'),[Customer],[Product Division ],"__Tons",SUM([Tons ])) VAR __max = MAXX(FILTER(__table,[Customer]=__customer),[__Tons]) RETURN MAXX(FILTER(__table,[Customer]=__customer && [__Tons]=__max),[Product Division ])Hi Anonymous
From your example, it seems the “given date range” here is before 2018/5/1, so i use "date" column in a slicer and select "before 2018/5/1".
First i create a calendar date table
Table2 = CALENDARAUTO()
please note not to create relationship between this date table with your table.
Then create measures in your table ( called Table1 below)
max = MAX(Table2[Date]) min = MIN(Table2[Date]) max purchase = CALCULATE(MAX(Table1[Tons ]),FILTER(ALLEXCEPT(Table1,Table1[Customer]),[Date ]>=[min]&&[Date ]<[max])) max-produce = CALCULATE(MAX(Table1[Product Division ]),FILTER(ALLEXCEPT(Table1,Table1[Customer]),[Tons ]=[max purchase]))
Best Regards
Maggie
HI-
I'm getting an Error on Max Purchase
A single value for column 'Date' in table 'Calendar 1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Can you assist?
Hi Anonymous
this error occurs for you put directly a column in a measure, but by design, you need to specify an aggregation such as min, max, count, or sum to get a single result.
Measure1=calendar[date] (error)
Measure2=max(calendar[date])
Do not worry you would get the max value for the [date] column, the formula of Measure2 only give the current value of each row. for example,
date Measure2
2018/1/1 2018/1/1
2018/4/1 2018/4/1
2018/5/1 2018/5/1
to get the max value of the [date] column, you should define the filter content.
for example
date Measure3
2018/1/1 2018/6/1
2018/2/1 2018/6/1
2018/3/1 2018/6/1
2018/4/1 2018/6/1
2018/5/1 2018/6/1
2018/6/1 2018/6/1
Measure3=calculate(max(calendar[date]),all(calendar))
Best Regards
Maggie
