Forum Discussion
Create Most Often field
Hi PBINewbie920 ,
Please try these measures:Primary Color % = VAR _customer = MAX('Table'[Customer]) VAR _color = MAX('Table'[Color]) VAR _count = CALCULATE ( COUNT ( 'Table'[Customer] ), FILTER ( ALL ( 'Table' ), 'Table'[Customer] = _customer && 'Table'[Color] = _color ) ) VAR _count_all = CALCULATE ( COUNT ( 'Table'[Customer] ), FILTER ( ALL ( 'Table' ), 'Table'[Customer] = _customer ) ) VAR _rate = DIVIDE ( _count, _count_all ) RETURN _rateFilter = VAR _max = MAXX(ALLEXCEPT('Table','Table'[Customer]),[Primary Color %]) VAR _result = IF([Primary Color %]=_max,1) RETURN _resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
PBINewbie920 Maybe:
Primary Color Measure =
VAR __Table = SUMMARIZE('Table',[Customer],[Color],"__Count",COUNTROWS('Table')
VAR __Max = MAXX(__Table,[__Count])
RETURN
MAXX(FILTER(__Table,[__Count]=__Max),[Color])
and you can also do this:
Primary Color % =
VAR __Total = COUNTROWS('Table')
VAR __Color = [Primary Color Measure]
VAR __Count = COUNTROWS(FILTER('Table',[Color] = __Color)
RETURN
DIVIDE(__Count, __Total, 0)
Greg_Deckler Hi! thank you!! I tried to use this in DAX as a column, and as a created measure, but both ways I keep getting an error:
PBINewbie920 Missed a closing paren:
Primary Color Measure = VAR __Table = SUMMARIZE('Table',[Customer],[Color],"__Count",COUNTROWS('Table')) VAR __Max = MAXX(__Table,[__Count]) RETURN MAXX(FILTER(__Table,[__Count]=__Max),[Color])Greg_Deckler Thanks! Looks like its not able to distinguish between the different Customers-- like customer B should be yellow. However it looks like its just picking the top from color in general: