Forum Discussion
Show text based on same row but different column
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
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.
8 Replies
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
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.
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
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
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.
