Forum Discussion
Create Measure Unaffected by Slicer(s)
I have a dataset similar to below. Each row represents an event and the Count column is always populated with a (numeric) 1.
I'm trying to create two measures counting the number of events, one that's affected by slicers on EventType and Flag_YN, the other affected by Flag_YN only. It should be fairly simple but I just can't get it to work.
For example, if a user has Flag_YN = Y and EventType = A, what I would like displayed is "2" and "8".
I've tried:
EventLevel = CALCULATE(SUM(Table[Count]),ALLEXCEPT(Table,Table[Flag_YN],Table[EventType]))
EventLevel = CALCULATE(VALUES(Table[Count]),ALLEXCEPT(Table,Table[Flag_YN],Table[EventType]))
EventLevel = CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[Flag_YN],Table[EventType]))
Also tried the above with REMOVEFILTERS. For the Flag_YN level measure I tried the above with the EventType portion removed.
No matter what I try both measures change with the EventType slicers and end up displaying the same numbers. What am I overlooking here? Would appreciate any help.
| EventID | EventType | Flag_YN | Count |
| 236 | A | Y | 1 |
| 336 | A | Y | 1 |
| 180 | B | Y | 1 |
| 41 | B | Y | 1 |
| 455 | B | N | 1 |
| 454 | C | Y | 1 |
| 203 | D | N | 1 |
| 79 | D | Y | 1 |
| 367 | D | Y | 1 |
| 94 | D | Y | 1 |
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.
first measure: = CALCULATE ( COUNTROWS ( Data ), ALLEXCEPT ( Data, Data[EventType], Data[Flag_YN] ) )second measure: = CALCULATE ( COUNTROWS ( Data ), ALLEXCEPT ( Data, Data[Flag_YN] ) )
2 Replies
- Jihwan_KimSuper User
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.
first measure: = CALCULATE ( COUNTROWS ( Data ), ALLEXCEPT ( Data, Data[EventType], Data[Flag_YN] ) )second measure: = CALCULATE ( COUNTROWS ( Data ), ALLEXCEPT ( Data, Data[Flag_YN] ) ) - AnonymousNot applicable
Thanks, not sure what I was doing wrong but creating a new set of measures using those formulas seemed to do the trick.