topic Re: Value that occurs most often in a calculated column in another table in DAX Commands and Tips

Value that occurs most often in a calculated column in another table

EricaK — Mon, 16 Jun 2025 11:47:33 GMT

Hi there all,

Fairly new to DAX and i'm struggling right now to get a result in a calculated column.

What do I have:

Table Treatments

TreatmentID	Treated by Employee ID	EpisodeID	CombineEpisodeEmployee
uniquenumber	Number	Number	CalculatedColumn = helpcolumn
1	301	11	11-301
2	301	11	11-301
3	304	11	11-304
4	304	21	21-304
5	304	21	21-304

Table episodes

EpisodeID	MostTreatedEmployeeID
UniqueNumber	CalculatedColumn
11	301
21	304

How can i calculate the MostTreatedEmployeeID?

I had it working in a reagular measure with the following code:

MostTreatedEmployeeID =
right(MAXX(TOPN(1,GROUPBY('Treatments','Treatments'[CombineEpisodeEmployee],"Count",COUNTX(CURRENTGROUP(), 'Treatments'[CombineEpisodeEmployee])), [Count], DESC), 'Treatments'[CombineEpisodeEmployee]),3)

Tho, when it is a measure I cannot use it in a matrix in the collumns or rows, which I want to. Or, can I?

I know that i have to put in the EpisodeID in the search somewhere, but i'm lost in the way how to...

Thanks in advance!

Re: Value that occurs most often in a calculated column in another table

SamsonTruong — Mon, 16 Jun 2025 19:52:57 GMT

Hi EricaK ,

One way to get around this is to replicate your logic as a calculated column. Here is your measure converted to a calculated column:

MostTreatedEmployeeID = VAR CurrentEpisode = Episodes[EpisodeID] VAR EpisodeTreatments = FILTER( Treatments, Treatments[EpisodeID] = CurrentEpisode ) VAR EmployeeFrequency = ADDCOLUMNS( SUMMARIZE(EpisodeTreatments, Treatments[Treated by Employee ID]), "Count", COUNTROWS(FILTER(EpisodeTreatments, Treatments[Treated by Employee ID] = EARLIER(Treatments[Treated by Employee ID]))) ) VAR TopEmployee = TOPN( 1, EmployeeFrequency, [Count], DESC, Treatments[Treated by Employee ID], ASC ) RETURN MAXX(TopEmployee, Treatments[Treated by Employee ID])

Once created, you can add this calculated column as a row or column in your matrix.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session

Re: Value that occurs most often in a calculated column in another table

FBergamaschi — Tue, 17 Jun 2025 06:39:20 GMT

Hi,

you cannot group a measure (put it in rows or columns in a mtarix) but you can group a column. So what you can do is group the EpisodeID from the Episode table and create a measure for the MostTreatedEmployeeID like follows:

Most Treated Employee ID =

VAR MaxNrTreatmentsByEmployee =

MAXX (

VALUES ( Treatments[Treated by Employee ID] ),

CALCULATE( COUNTROWS( Treatments ) )

)

VAR EployeeTreatments =

ADDCOLUMNS(

VALUES ( Treatments[Treated by Employee ID] ),

"@NrTreats", CALCULATE( COUNTROWS( Treatments ) )

)

VAR EmployeesMaxTreats =

FILTER (

EployeeTreatments,

[@NrTreats] = MaxNrTreatmentsByEmployee

)

RETURN

CONCATENATEX( EmployeesMaxTreats, [Treated by Employee ID], " / " )

Please note two things

1 - you cannot assume that the Most Treated Employee is a single one, and I considered the possibiliy they are mutiple

2 - To Improve your model, I suggest to create a table Employee and if you want I can change the above code considering this change

If this solves, please mark this post as a solution

Thanks!

Re: Value that occurs most often in a calculated column in another table

v-echaithra — Fri, 20 Jun 2025 08:33:15 GMT

Hi EricaK ,

Thank you FBergamaschi , SamsonTruong for your inputs.

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

Re: Value that occurs most often in a calculated column in another table

EricaK — Mon, 23 Jun 2025 08:43:35 GMT

Hi,

Thank you so much! This is a real good help for me!

The measure that you made is to put as a calculated column, right? Well, it works there 😉 It also works as a general measure, but I can't

Your notes:

1 - Most treated is a single one: you are right! in 99% of the cases it is, and for the last percent I wanted to add (later) an extra part of the formula. If there are 2 (or more) then use another column -> MainContact. I'm open for help in this part 😉
2 - I do have a table Employee, but I don't see how you would change the code above? Use names in stead of ID numbers? Yes, I was planning on doing that. Wanted to get ID's first and a lookup isn't that hard.

Your response now gives all the ID's that have worked in this Episode. How can I get the one with the most treatments?

thanks in advance!

Re: Value that occurs most often in a calculated column in another table

EricaK — Mon, 23 Jun 2025 08:44:19 GMT

Unfortunately your code gives an #ERROR and I have trouble finding where it goes wrong. Thanks for helping tho! Really appreciated!

Re: Value that occurs most often in a calculated column in another table

FBergamaschi — Mon, 23 Jun 2025 13:15:20 GMT

Hi Erika K

below my answers

Hi,

Thank you so much! This is a real good help for me!

The measure that you made is to put as a calculated column, right?

FB no, I am using a column you have already, EpisodeID from the Episode table

It also works as a general measure, but I can't

FB what you can't is unclear to me

Your notes:

FB my code already considers this possibility

2 - I do have a table Employee, but I don't see how you would change the code above? Use names in stead of ID numbers? Yes, I was planning on doing that. Wanted to get ID's first and a lookup isn't that hard.

FB I meant connecting the Employee dimension table to the fact tables and subtitute in the DAX code the column Treatments[Treated by Employee ID] that comes from the fact table with the Employee ID from the Employee dimension table

Your response now gives all the ID's that have worked in this Episode. How can I get the one with the most treatments?

FB my code gives you already the ID of the employees with most treatments of that episode

thanks in advance!

FB you are welcome, if this is a solution please give kudos and mark as a solution, otherwise I am here at disposal

Re: Value that occurs most often in a calculated column in another table

v-echaithra — Wed, 25 Jun 2025 06:17:03 GMT

Hi EricaK ,

Regards,
Chaithra.

Re: Value that occurs most often in a calculated column in another table

v-echaithra — Fri, 27 Jun 2025 06:01:57 GMT

Hi EricaK ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

Re: Value that occurs most often in a calculated column in another table

EricaK — Mon, 30 Jun 2025 07:52:19 GMT

Thanks! I don't know if this is an automated message? But I only have the opportunity to work on this on Mondays 😉 I will accept as solution when it's done 🙂

Re: Value that occurs most often in a calculated column in another table

EricaK — Mon, 30 Jun 2025 08:04:47 GMT

Thank you so much!

2 Things that are still open for me now:

The DAX code you made for me, do I have to use that as a calculated column? Yes, right?
If I use it this way, it will give as response all the ID's that have worked on that episode - I think it is because of the CONCATENATEX( ), but I have no idea where to replace it with.

It also works as a general measure, but I can't

FB what you can't is unclear to me

Haha, I wasn't done typing there. Excuse me.

I can't use a general measure in a matrix in the collumns or rows, which I want to. Therefor I think it should be in a calculated column.

Most treated if there are 2 (or more) is solved

In regards to the Employee in fact table - it is part of the export that has the Employee included. I think this is good right now.

Re: Value that occurs most often in a calculated column in another table

v-echaithra — Thu, 03 Jul 2025 09:07:01 GMT

Hi EricaK ,

Try again with this DAX as a calculated column.

MostTreatedEmployeeID__12 =

VAR CurrentEpisode = 'Table'[EpisodeID]

VAR EmployeeCounts =

ADDCOLUMNS(

SUMMARIZE(

FILTER('Table', 'Table'[EpisodeID] = CurrentEpisode),

'Table'[Treated by Employee ID]

),"TreatCount",

CALCULATE(COUNTROWS('Table'),

FILTER(

'Table',

'Table'[EpisodeID] = CurrentEpisode &&

'Table'[Treated by Employee ID] = EARLIER('Table'[Treated by Employee ID])

)))

VAR TopEmployee =

TOPN(

EmployeeCounts,

[TreatCount], DESC,

'Table'[Treated by Employee ID], ASC

)

RETURN

MAXX(TopEmployee, 'Table'[Treated by Employee ID])

Please refer to the pbix file attatched.

Re: Value that occurs most often in a calculated column in another table

v-echaithra — Tue, 08 Jul 2025 06:51:43 GMT

Hi EricaK ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

Re: Value that occurs most often in a calculated column in another table

EricaK — Tue, 08 Jul 2025 07:46:41 GMT

Yes, this works! Thank you so much 😄