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 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.

[v-echaithra · Accepted solution]

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(

        1,

        EmployeeCounts,

        [TreatCount], DESC,

        'Table'[Treated by Employee ID], ASC

    )

RETURN

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

Please refer to the pbix file attatched.

[EricaK]

Yes, this works! Thank you so much 😄 

[v-echaithra]

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.

[EricaK]

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 🙂

[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

[EricaK]

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.

[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!