Show text based on same row but different column

[Anonymous]

Hello,

could you please help me with this issue

I have a table "Table_Injuries" and I also created count for each unique names/locations.

Name      Name count     Location              Location count

Peter       3                       Production          2

Peter       3                       Production          2

Peter       3                      Office                   4

John       2                       Office                  4

John       2                       Office                  4

Joe         1                       Kitchen                3
Roger     4                       Kitchen                3

Roger     4                       Office                  4

Roger     4                       Kitchen                3

Roger    4                        Hall                     1

I need to visualise the "most common name" - in this case Roger and in another visualisation the "most common location" - in this case Office.

Could you please help me, what to write into "Measure"? Somehow like this: Take in the column "Name count" the biggest number, check same row, but different column "Name" and visualise only this name.

Thank you

Jan

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think you want "most common name" and "most common location" to be dynamic. So I create two measures to count and two measures to show results.

Like this:

namecount = COUNTX(FILTER(ALLSELECTED('Table'),[Name]=SELECTEDVALUE('Table'[Name])),[Name])

most common name = MAXX(FILTER(ALLSELECTED('Table'),[namecount]=MAXX(ALLSELECTED('Table'),[namecount])),[Name])

locationcount = COUNTX(FILTER(ALLSELECTED('Table'),[Location]=SELECTEDVALUE('Table'[Location])),[Location])

most common location = MAXX(FILTER(ALLSELECTED('Table'),[locationcount]=MAXX(ALLSELECTED('Table'),[locationcount])),[Location])

I suggest you use slicer instead of 'filter on this page', because the latter may have more hard effects.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-janeyg-msft 

perfect, it works fine now. Thank you much gentlemas for your help.

Jan

[v-janeyg-msft]

Hi, @Anonymous 

You are welcome. It’s my pleasure to answer for you. If this case has been resolved, Can you Accept my answer as the solution to help the other members find it more quickly.😊

Best Regards

Janey Guo

[Anonymous]

Hello,

thank you for your help - the measure works fine. Just one thing, the original table has 20 colums and some of them are as filters for example "Production group" and "Date". If I filter for example date "2021" the most common name and location doesnt fit the value. There is shown wrong name for example.

Thank you

Jan

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I am not sure whether I understood your question correctly.

Maybe I am missing some point from your first question.

Do you want to use the first measure to calculate the same or similar result from a totally different table?

What I see is that the table from your first question and the table from your second question is totally different. And I cannot understand what result do you want to get by using the first measure by applying it to the second table.

Please correct me if I am wrong, but the measure is showing the correct value. I think you applied the correct measure to the wrong table.

Please apply the measure to the first table that you showed in the first question.

Thank you.

[Anonymous]

Thank you, I am sorry, I am new in Power BI, the problem is:

If I set up a filter, for example "Date" or "Production group" the most common location/name do not corresponding to the filtered value.

Maybe I am doing something wrong.

Thank you very much for your help

[Anonymous]

There is souce table:

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the measures.

I put those two measures into each card visualization.

Most common name =

CALCULATE (

SELECTEDVALUE ( Injuries[Name] ),

FILTER ( ALLSELECTED(Injuries), Injuries[Name Count] = MAX ( Injuries[Name Count] ) )

)

Most common location =

CALCULATE (

SELECTEDVALUE ( Injuries[Location] ),

FILTER ( ALLSELECTED(Injuries), Injuries[Location Count] = MAX ( Injuries[Location Count] ) )

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM