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

[EricaK]

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!

[v-echaithra]

Hi @EricaK ,

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.

[v-echaithra]

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.

[FBergamaschi]

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!

[EricaK]

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!

[FBergamaschi]

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:

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 

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

[SamsonTruong]

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

[EricaK]

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