help with answering this question for a dataset -Which injury location is the most common for each?

[lastnn30]

Hi

I have this dataset and I am trying to answer the following question without writing DAX? just create a visual with filter/silicer etc

Which injury location is the most common for each plant?

I solved it by creating matrix but it is not showing what is the most common injury. I have to find that manually. Please see the screenshot, I am also attaching the dataset.

This is the DataSet link

https://drive.google.com/file/d/1pOEqVBabfepk-BvxUT45S91VuHWDw4Fp/view?usp=sharing

[tackytechtom · Accepted solution]

Hi @lastnn30 ,

Is there a special reason why you don't want to use DAX for this? 🙂 I would have created a measure to solve your issue, so let me know if you need some help with creating one. Otherwise, here a shot on how to solve it just visually:

Option 1

On the left, a slicer for plant which only allows single select. On the right, a table visual including color coding on the max of the count of incident type. 

Option 2

Left, the slicer and on the right a table with a Top N (Top 1) filter by Count of Incident Type.

Was it kinda this what you meant?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom · Accepted solution]

Hi @lastnn30 ,

I think, this is what you would like to achieve, isn't it?

Here a little (or rather detailed) walkthrough on how I would do it:

1) create the count measure:

DAX:

MeasureCountIncidentType = 
COUNT ( SafetyData1[Incident Type] )

PBI:

As you can see, the measure does the same as dragging in the column and then changing it to COUNT in the Values pane. Still, I would recommend to always create a dedicated measure instead of using the "drag in a column approach". Also, the measure returns the same result as using a COUNTROWS ( SafetyData1 ) since we are actually interested in how many incidents (= rows) occured. Anyway, we can keep this measure as it is for now since this was the one from the start.

2) create the actual measure showing which injury type appeared the most per plant.

Here the whole measure, but I will guide you through each of the parts:

MeasureMaxCountIncidentTypePerPlant = 
VAR _helpTable = 
SUMMARIZE ( 
    ALLEXCEPT ('SafetyData1', SafetyData1[Plant]), 
    'SafetyData1'[Injury Location], 
    "Measure", [MeasureCountIncidentType]
)
VAR _maxCount = 
CALCULATE ( 
    MAXX ( _helpTable, [Measure] )
)
RETURN
CALCULATE ( MAX ( 'SafetyData1'[Injury Location] ), FILTER ( _helpTable, [Measure] = _maxCount  ))

The _helpTable would look like this in PBI:

It does the summarization on injury location and displays the overall count for each of them. 

Using _maxCount solely in PBI would obviosuly show the maximum of the table above:

It gets interesting though when using the _maxCount together with the plant attribute because this filters the _helpTable and consequently shows the maximum count of injury location per plant:

So we are almost there. All we need to do now is to look per plant which injury location matches the maximum count of injury location or in more simple words, what is the most frequent injury location per plant. That equals the last line of the measure.

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[lastnn30]

Many thanks Tom. I appreciate all the help. Thanks again.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[lastnn30]

Thank you very much Ashish for your help. I appreciate it.

[Ashish_Mathur]

You are welcome.

[tackytechtom · Accepted solution]

Hi @lastnn30 ,

I think, this is what you would like to achieve, isn't it?

Here a little (or rather detailed) walkthrough on how I would do it:

1) create the count measure:

DAX:

MeasureCountIncidentType = 
COUNT ( SafetyData1[Incident Type] )

PBI:

As you can see, the measure does the same as dragging in the column and then changing it to COUNT in the Values pane. Still, I would recommend to always create a dedicated measure instead of using the "drag in a column approach". Also, the measure returns the same result as using a COUNTROWS ( SafetyData1 ) since we are actually interested in how many incidents (= rows) occured. Anyway, we can keep this measure as it is for now since this was the one from the start.

2) create the actual measure showing which injury type appeared the most per plant.

Here the whole measure, but I will guide you through each of the parts:

MeasureMaxCountIncidentTypePerPlant = 
VAR _helpTable = 
SUMMARIZE ( 
    ALLEXCEPT ('SafetyData1', SafetyData1[Plant]), 
    'SafetyData1'[Injury Location], 
    "Measure", [MeasureCountIncidentType]
)
VAR _maxCount = 
CALCULATE ( 
    MAXX ( _helpTable, [Measure] )
)
RETURN
CALCULATE ( MAX ( 'SafetyData1'[Injury Location] ), FILTER ( _helpTable, [Measure] = _maxCount  ))

The _helpTable would look like this in PBI:

It does the summarization on injury location and displays the overall count for each of them. 

Using _maxCount solely in PBI would obviosuly show the maximum of the table above:

It gets interesting though when using the _maxCount together with the plant attribute because this filters the _helpTable and consequently shows the maximum count of injury location per plant:

So we are almost there. All we need to do now is to look per plant which injury location matches the maximum count of injury location or in more simple words, what is the most frequent injury location per plant. That equals the last line of the measure.

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom · Accepted solution]

Hi @lastnn30 ,

Is there a special reason why you don't want to use DAX for this? 🙂 I would have created a measure to solve your issue, so let me know if you need some help with creating one. Otherwise, here a shot on how to solve it just visually:

Option 1

On the left, a slicer for plant which only allows single select. On the right, a table visual including color coding on the max of the count of incident type. 

Option 2

Left, the slicer and on the right a table with a Top N (Top 1) filter by Count of Incident Type.

Was it kinda this what you meant?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[lastnn30]

Thank you so much for the DAX. I will study it carfully. Can not thank you enough. Sorry for bothering you once again, about the color coding the max (without using DAX), how did you do that? I tried conditional formatting that column but I did not get the same result as yours? Thank you once again.

[tackytechtom · Accepted solution]

Hi @lastnn30 ,

Here the settings for the background color conditional formatting:

Do not forget to mark one or multiple answers as a solution so people have it easier to find it in the future. Also, @Ashish_Mathur 's solution is very neat! His approach returns even multiple incident types if there are more than one maximum!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Ashish_Mathur]

Thank you for your kind words @tackytechtom

[lastnn30]

Thank you very much Tom for the help. The reason is that I am not familiar with DAX and now I am trying to understand it. So, if you do not mind, I would appreciate if you can show me the code (measure) so I can learn from it. I am trying to learn DAX by examples. Again thanks for you help. I appreciate it. Thanks