Forum Discussion
Using the Correct DAX to count multiple rows as 1
Bnb99 create a measure m1 = distinctcount(table1[recordnumber]) assuming record number is going to be unique accross all the injuries related to 1 persion in the visual level. in the chart visual filters or page level filter just select recordable = true
So what I am trying to measure is the number of incidents in a given month. (the image above in pink shows what I want to show in the visual.). In the previous post shows the column highlighted in grey, the incident "record number". If you move to the next column to the right, it identifes the body part that was injured. The data spilts up the incidents in to multiple rows if multiple body parts are entered in to the database, hence why you see three "312" record numbers, but it's only 1 incident. The final column shows true or false values. this is indicating that the incident resulted in an OSHA recordable (true meaning, yes it was an recordable incident). Currently, without any filtering, calculating, DAX or grouping the chart would show I have 3 OSHA recordables, but I really have one. How can I get the data, to look at the table and recognize that this as one event?
I want to avoid grouping as I noticed when using this feature, and more data is entered, the new data does not automatically get grouped, and I have to manually fix it. (unless I am doing that wrong....remember I am very new at all this!) I was hoping using distincint count, and some kind of "filter" DAX would help.
Hopefully this clears up my question.