Measure for max in category without using category filter

[dalmar22]

So I have dashboard in which I rank companies in several Aspects. Each company belongs to one Industry.

What I want to do is select company in Company slicer and see on a line chart score for that company in different Aspects. Besides that I want to have another line in that chart, which would be showing Maximum Score for the industry from which I selected the company. Currently  the Maximum Score  works for me correcctly only when I select both company and industry, even though once I select company I can select only one industry as all the others are filtered out. What I want is to select company without need to select industry.

I defined the measure for Maximum score for industry following:

Max_score_industry = CALCULATE(MAX(DataTable[Score]),ALLEXCEPT(DataTable,DataTable[Aspect],DataTable[Industry]))

Hope I am clear enough, all data is in one simple table. Any idea how should I define my Max_score_industry measure or what else I need to change?

Thank you

[Ashish_Mathur]

Hi,

Share a simple dataset and show the expected result in a simple Table format.

[dalmar22]

Hi @Ashish_Mathur @v-easonf-msft ,

please find my simplified file, desription of the problem is included. Thanks a lot for your suggestions.

[Ashish_Mathur]

Hi,

You  may download my PBI file from here.  See the result of the measure "Max_score_industry try".  It works even without selecting the Industry.

Hope this helps.

[v-easonf-msft]

Hi,  @dalmar22 

Did you want to have another line in the chart?

Maybe you can try to create another Axis and apply it to  line chart visual.
For example:

Axis = UNION(DISTINCT('DataTable'[Industry]),SELECTCOLUMNS({{"0"},{"Z"}},"Industry",[Value]))

Then you need to  create the corresponding measure "Max_score_industry"  as value.

Here is  demo .   ( please ignore the Measure "Max_score_industry" in demo,it is not right. ) This demo is  just  used to better express my idea .Hope it can bring you some ideas.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dalmar22]

Hi @v-easonf-msft ,

thanks for your suggestion, I am afraid the file you attached is not the correct one, as i dont see anywhere "Max_score_industry".

Can you please check?

Thanks a lot.

[v-easonf-msft]

Hi , @dalmar22 

Sorry for my mistake.Please check it here.

Here I just put forward my idea, please ignore the measure I wrote, because it is incorrect, it still needs to be modified .

Best Regards,
Community Support Team _ Eason

[parry2k]

@dalmar22 remove INdustry column from ALLEXCEP  function.

[dalmar22]

Thanks for respond @parry2k , however when I do that the measure shows maximum across all the companies. What I need is to show only maximum for the industry from which I selected company.