DAx to Create a dynamic Dimension Classification based on another dimension

[Anonymous]

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 

[v-juanli-msft · Accepted solution]

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

[Anonymous]

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? 

[v-juanli-msft]

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

[Greg_Deckler]

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