Forum Discussion
counting occurrences
Hi,
I have two fields ID and outcome. Each ID can have more than one outcome and each outcome is on a separate row (example below)
| ID | Outcome |
| 1 | A |
| 1 | B |
| 2 | C |
| 3 | A |
| 3 | C |
| 4 | B |
| 4 | C |
| 4 | D |
I would like to find out the number of occurrences of a number of outcomes
Example above would have following results (ID in brackets just to help explain, they will not be in the output)
| Number of Outcomes | Number of Occurrences |
| 1 | 1 (ID 2) |
| 2 | 2 (ID 1 and 3) |
| 3 | 1 (ID 4) |
Can this be done with a measure or will I need to create interim tables?
Thanks
Hi,
I suggest ( and agree with you) having an additional table like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Number of Occurreces measure: = VAR _newtable = ADDCOLUMNS ( DISTINCT ( Data[ID] ), "@count", CALCULATE ( COUNTROWS ( Data ) ) ) VAR _filtertable = FILTER ( _newtable, [@count] = MAX ( 'Axis'[Axis] ) ) RETURN COUNTROWS ( _filtertable )
2 Replies
- Jihwan_Kim
Super User
Hi,
I suggest ( and agree with you) having an additional table like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Number of Occurreces measure: = VAR _newtable = ADDCOLUMNS ( DISTINCT ( Data[ID] ), "@count", CALCULATE ( COUNTROWS ( Data ) ) ) VAR _filtertable = FILTER ( _newtable, [@count] = MAX ( 'Axis'[Axis] ) ) RETURN COUNTROWS ( _filtertable )That worked perfectly! thank you 😀
