DAX - Retrieve a value in one column for max occurence of distinct values based on another column

[FrancescaLu]

Hi,

First post, sorry for the confusing subject, in fact i'm really struggling to find a solution. Hopefully, somebody could help me find the correct DAX statement.

My goal is to have a column with the value of another column associated to the max occurence of distinct values based on another column. I can explain better with an example.

My set of data is something like this table:

ID	Value	Column1
A	3	X
A	3	X
A	3	X
B	1	X
C	1	X
D	2	Y
E	4	Y
E	4	Y
F	2	Y

What I want is to select "Value" associated to the max number of different ID, and I want to do this for each different value of "Column1", so the result has to be like this:

ID	Value	Column1	Result
A	3	X	1
A	3	X	1
A	3	X	1
B	1	X	1
C	1	X	1
D	2	Y	2
E	4	Y	2
E	4	Y	2
F	2	Y	2

Please, advise if not clear enough.

Thank you a lot!

[v-jayw-msft · Accepted solution]

Hi @FrancescaLu ,

Please check below formulas.

count_ = CALCULATE(DISTINCTCOUNT('Table'[ID]),ALLEXCEPT('Table','Table'[Value],'Table'[Column1]))

Measure = 
var max_ = MAXX(ALLEXCEPT('Table','Table'[Column1]),[count_])
return
CALCULATE(MAX('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Column1]),[count_]=max_))

Best Regards,

Jay

[FrancescaLu]

Thank you @v-jayw-msft, this is a real good solution, but i need the result displayed in a new column contained in the table. The problem is that with a measure i can only see it on dashboards.

[v-jayw-msft]

Hi @FrancescaLu ,

The above formulas also work for calculated column.

Best Regards,

Jay

[parry2k]

sorry, I'm still not very clear about what you are trying to achieve. Maybe it's me

[FrancescaLu]

I try to explain with other words. I want to retrieve for each value of Column1, the value contained in "Value" that recurs the maximum number of times for different ID

[parry2k]

@FrancescaLu not really clear, why the max number for A will be 1, what you are referring to as number?

[FrancescaLu]

Hi, thank you fir answering!

I would put 1 also for A becouse for X i got A with value 3, but it appearse only one time. Instead, value 1 appearse 2 times, with B and C.